|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Can someone help with a formula to return a value from this lookup.
I have created an example of the lookup I need.
The target range is a range of (say) three columns. I want to lookup a
value from the third column and return the value from the first cell in
the corresponding row.
eg three columns (A, B, C)
4 rows (1,2,3,4)
Description Qty Code
Desc A 3 1
Desc B 4 2
Desc C 2 3
Desc D 8 4
if the target is "3" I want to return "Desc C"
if the target is "4" I want to return "Desc D"
Thanks
|
|
Hi Tony,
=INDEX(A1:C5,MATCH(3,C1:C5,FALSE),1)
should do the job ...
HTH
Cheers
Carim
|
|
Hi Carim,
Is it true that we can't use VLOOKUP here because code is not the *leftmost* column?
Thanks.
Epinn
"Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159170684.668881.201420[ at ]e3g2000cwe.googlegroups.com...
Hi Tony,
=INDEX(A1:C5,MATCH(3,C1:C5,FALSE),1)
should do the job ...
HTH
Cheers
Carim
|
|
Hi Epinn,
If I am mistaken, this was at least the rule ....
in order to vlookup "on the left", one had to combine match and index
to come up with the result ...
HTH
Cheers
Carim
|
|
I have been hoping that I can use VLOOKUP and forget about INDEX and MATCH. :) So, today I am reminded that there are circumstances that I can't use VLOOKUP. I wonder if VLOOKUP is faster.
Learning everyday ......
Epinn
"Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
Hi Epinn,
If I am mistaken, this was at least the rule ....
in order to vlookup "on the left", one had to combine match and index
to come up with the result ...
HTH
Cheers
Carim
|
|
It just dawned on me that the other rule might be this.
VLOOKUP - Leftmost column must be sorted in ascending order.
MATCH - Array doesn't have to be sorted.
Tony, I hope you don't mind me showing up. When I learn, I usually like to look at the big picture.
Epinn
"Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
Hi Epinn,
If I am mistaken, this was at least the rule ....
in order to vlookup "on the left", one had to combine match and index
to come up with the result ...
HTH
Cheers
Carim
|
|
Actually, if the last argument to vlookup is false (or 0) meaning you want to
find an exact match, the lookup range does not need to be sorted.
--
Kevin Vaughn
"Epinn" wrote:
[Quoted Text] > It just dawned on me that the other rule might be this.
>
> VLOOKUP - Leftmost column must be sorted in ascending order.
>
> MATCH - Array doesn't have to be sorted.
>
> Tony, I hope you don't mind me showing up. When I learn, I usually like to look at the big picture.
>
> Epinn
>
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
>
>
>
|
|
Epinn,
follow this link and explore the site:
http://www.decisionmodels.com/optspeede.htm
if you are interested in the speed of calculations.
VLOOKUP is slightly faster than an INDEX/MATCH combination, but the
latter is far more flexible and can improve calculation speed in many
situations.
Hope this helps.
Pete
Epinn wrote:
[Quoted Text] > I have been hoping that I can use VLOOKUP and forget about INDEX and MATCH. :) So, today I am reminded that there are circumstances that I can't use VLOOKUP. I wonder if VLOOKUP is faster.
>
> Learning everyday ......
>
> Epinn
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
|
|
Of course, I forgot about that. But for MATCH, it *never* requires sorting, right?
See what I mean about learning the big picture. I think we all learn better when we connect. Thanks for reminding me.
Cheers,
Epinn
"Kevin Vaughn" <KevinVaughn[ at ]discussions.microsoft.com> wrote in message news:20330308-6307-4117-A7EE-C30BBC2E1007[ at ]microsoft.com...
Actually, if the last argument to vlookup is false (or 0) meaning you want to
find an exact match, the lookup range does not need to be sorted.
--
Kevin Vaughn
"Epinn" wrote:
[Quoted Text] > It just dawned on me that the other rule might be this.
>
> VLOOKUP - Leftmost column must be sorted in ascending order.
>
> MATCH - Array doesn't have to be sorted.
>
> Tony, I hope you don't mind me showing up. When I learn, I usually like to look at the big picture.
>
> Epinn
>
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
>
>
>
|
|
Please ignore my previous posts on sorting.
If I understand correctly, both VLOOKUP and MATCH require sorting in the same manner.
FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending order
I think I am okay now.
Epinn
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message news:O4txgxO4GHA.2464[ at ]TK2MSFTNGP06.phx.gbl...
Of course, I forgot about that. But for MATCH, it *never* requires sorting, right?
See what I mean about learning the big picture. I think we all learn better when we connect. Thanks for reminding me.
Cheers,
Epinn
"Kevin Vaughn" <KevinVaughn[ at ]discussions.microsoft.com> wrote in message news:20330308-6307-4117-A7EE-C30BBC2E1007[ at ]microsoft.com...
Actually, if the last argument to vlookup is false (or 0) meaning you want to
find an exact match, the lookup range does not need to be sorted.
--
Kevin Vaughn
"Epinn" wrote:
[Quoted Text] > It just dawned on me that the other rule might be this.
>
> VLOOKUP - Leftmost column must be sorted in ascending order.
>
> MATCH - Array doesn't have to be sorted.
>
> Tony, I hope you don't mind me showing up. When I learn, I usually like to look at the big picture.
>
> Epinn
>
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
>
>
>
|
|
Thanks for the answer.
I am a firm believer in the asking of "dumb" questions. I'm glad that
this "dumb" question generated a lot of discussion.
I have not used Index or match before, thinking that ?loopup wa the
only way to do a lookup.
The formula works a treat.
Thanks again.
TonyS.
Tony Strazzeri wrote:
[Quoted Text] > Can someone help with a formula to return a value from this lookup.
>
> I have created an example of the lookup I need.
> The target range is a range of (say) three columns. I want to lookup a
> value from the third column and return the value from the first cell in
> the corresponding row.
>
> eg three columns (A, B, C)
> 4 rows (1,2,3,4)
>
> Description Qty Code
> Desc A 3 1
> Desc B 4 2
> Desc C 2 3
> Desc D 8 4
>
> if the target is "3" I want to return "Desc C"
> if the target is "4" I want to return "Desc D"
>
> Thanks
|
|
Hi
Not correct.
From Vlookup Help
"Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or
omitted, an approximate match is returned. In other words, if an exact
match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If
one is not found, the error value #N/A is returned"
Where possible, Sorted lookup tables are preferable, as all of the
functions Match, Vlookup and Hlookup will perform faster, but this has
no bearing upon whether you specify the True or False parameter, it all
depends upon the type of data and the result that you want.
--
Regards
Roger Govier
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uY2Qw%23P4GHA.2424[ at ]TK2MSFTNGP06.phx.gbl...
Please ignore my previous posts on sorting.
If I understand correctly, both VLOOKUP and MATCH require sorting in the
same manner.
FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending
order
I think I am okay now.
Epinn
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:O4txgxO4GHA.2464[ at ]TK2MSFTNGP06.phx.gbl...
Of course, I forgot about that. But for MATCH, it *never* requires
sorting, right?
See what I mean about learning the big picture. I think we all learn
better when we connect. Thanks for reminding me.
Cheers,
Epinn
"Kevin Vaughn" <KevinVaughn[ at ]discussions.microsoft.com> wrote in message
news:20330308-6307-4117-A7EE-C30BBC2E1007[ at ]microsoft.com...
Actually, if the last argument to vlookup is false (or 0) meaning you
want to
find an exact match, the lookup range does not need to be sorted.
--
Kevin Vaughn
"Epinn" wrote:
[Quoted Text] > It just dawned on me that the other rule might be this.
>
> VLOOKUP - Leftmost column must be sorted in ascending order.
>
> MATCH - Array doesn't have to be sorted.
>
> Tony, I hope you don't mind me showing up. When I learn, I usually
> like to look at the big picture.
>
> Epinn
>
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message
> news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
>
>
>
|
|
Roger,
Thanks for responding.
I did read Help and other resources before I post so I was very much aware of the 1st paragraph of your post. I also agree that it is preferable to sort the table/array first to make it efficient. Biff gave me the headsup previously and thanks to Pete, I also read this link on optimal performance.
http://www.decisionmodels.com/optspeede.htm
So, you probably ask why I said this in the post.
"FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending order"
I was just trying to "paraphrase the rules" cited in Help. Maybe I did not do a good job.
Following is from Help.
********************************************************************************
a.. If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in **ascending order** ........
a.. If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be **in any order**.
a.. If TRUE or omitted .......The values in the first column of table_array must be placed in **ascending sort order**; otherwise, VLOOKUP may not give the correct value.
a.. If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array **do not need to be sorted**.
*********************************************************************************
I was trying to say the exact same thing in my post. So, I don't understand why it wasn't correct. Was it poor choice of words on my part?
If it is TRUE, we have no choice but to sort in ascending order first. If it is FALSE, we can get away without sorting although it is probably more efficient to sort first. This is my interpretation from reading Help etc. and I just wanted to lay out the "rules" in my own words in the post.
It is a very good idea that you said what you said so that others can see the whole picture. When I said "seeing the big picture" in one of my posts, I was referring to comparing VLOOKUP with Match. At one point, I made a mistake thinking that there was a difference between the two in terms of sorting. Later, I straightened out myself and requested others to ignore my prior posts on sorting and ended up with the last post.
I hope I haven't confused you or others more. This thread has helped me reinforce what I have learned so far. Thank you all for your support. Much appreciated.
Epinn
"Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message news:OaJt7tU4GHA.1252[ at ]TK2MSFTNGP04.phx.gbl...
Hi
Not correct.
From Vlookup Help
"Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or
omitted, an approximate match is returned. In other words, if an exact
match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If
one is not found, the error value #N/A is returned"
Where possible, Sorted lookup tables are preferable, as all of the
functions Match, Vlookup and Hlookup will perform faster, but this has
no bearing upon whether you specify the True or False parameter, it all
depends upon the type of data and the result that you want.
--
Regards
Roger Govier
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uY2Qw%23P4GHA.2424[ at ]TK2MSFTNGP06.phx.gbl...
Please ignore my previous posts on sorting.
If I understand correctly, both VLOOKUP and MATCH require sorting in the
same manner.
FALSE - no sorting required
TRUE - column where lookup_up value is in must be sorted in ascending
order
I think I am okay now.
Epinn
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:O4txgxO4GHA.2464[ at ]TK2MSFTNGP06.phx.gbl...
Of course, I forgot about that. But for MATCH, it *never* requires
sorting, right?
See what I mean about learning the big picture. I think we all learn
better when we connect. Thanks for reminding me.
Cheers,
Epinn
"Kevin Vaughn" <KevinVaughn[ at ]discussions.microsoft.com> wrote in message
news:20330308-6307-4117-A7EE-C30BBC2E1007[ at ]microsoft.com...
Actually, if the last argument to vlookup is false (or 0) meaning you
want to
find an exact match, the lookup range does not need to be sorted.
--
Kevin Vaughn
"Epinn" wrote:
[Quoted Text] > It just dawned on me that the other rule might be this.
>
> VLOOKUP - Leftmost column must be sorted in ascending order.
>
> MATCH - Array doesn't have to be sorted.
>
> Tony, I hope you don't mind me showing up. When I learn, I usually
> like to look at the big picture.
>
> Epinn
>
>
> "Carim" <carimfam[ at ]yahoo.com> wrote in message
> news:1159179788.975677.32160[ at ]i42g2000cwa.googlegroups.com...
> Hi Epinn,
>
>
> If I am mistaken, this was at least the rule ....
> in order to vlookup "on the left", one had to combine match and index
> to come up with the result ...
>
> HTH
> Cheers
> Carim
>
>
>
|
|
|