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" wrote in message ups.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" wrote in message ups.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" wrote in message ups.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:

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" wrote in message ups.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:
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" wrote in message ups.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" wrote in message ...
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:

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" wrote in message ups.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" wrote in message ...
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" wrote in message ...
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:

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" wrote in message ups.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:
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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:

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" wrote in message
ups.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" wrote in message ...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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:

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" wrote in message
ups.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

Followup question.

First thanks for the help on this. It works well.

The followup I need help with is to do with formatting/limit of the
result.

If the source/result cell is empty the formula using INDEX returns a
zero "0". What I want is for it to return a blank/empty string.

I tried two things to fix that but they both cause other problems.
What I tried is;

1. formatting the source/result cell as TEXT works unless the cell has
more than 255 characters. In that case the source/result cell displays
overflow (fills the cell with "#").

2. I tried leaving the source/result cell formatted as general but
changed the formula to return text by converting the result using the
T function. The problem here is that if the cell contains more that
255 characters the result (in the destination cell) is blank.

I would appreciate any help to overcome this problem. Is there another
function I can use to convert the result to a string even if it is
longer than 255?

Cheers
TonyS.


Carim wrote:
Hi Tony,

=INDEX(A1:C5,MATCH(3,C1:C5,FALSE),1)

should do the job ...
HTH
Cheers
Carim

Hi Tony

If it is purely cosmetics, and you don't want the 0 to show, then you
could use ToolsOptionsGeneral and untick Display Zero.
Alternatively, you could set a conditional format on the cell with the
formula, so that if the result is 0, then make the font White on a white
background so it doesn't show.

If you really do need a Null value as a result rather than 0, then wrap
the whole formula in an IF() statement.
=IF(INDEX(A1:C5,MATCH(E1,C1:C5,FALSE),1)=0,
"",INDEX(A1:C5,MATCH(E1,C1:C5,FALSE),1))
I used cell E1 to hold my test value as opposed to hardcoding "3" into
the formula

--
Regards

Roger Govier


"Tony Strazzeri" wrote in message
oups.com...
Followup question.

First thanks for the help on this. It works well.

The followup I need help with is to do with formatting/limit of the
result.

If the source/result cell is empty the formula using INDEX returns a
zero "0". What I want is for it to return a blank/empty string.

I tried two things to fix that but they both cause other problems.
What I tried is;

1. formatting the source/result cell as TEXT works unless the cell has
more than 255 characters. In that case the source/result cell
displays
overflow (fills the cell with "#").

2. I tried leaving the source/result cell formatted as general but
changed the formula to return text by converting the result using the
T function. The problem here is that if the cell contains more that
255 characters the result (in the destination cell) is blank.

I would appreciate any help to overcome this problem. Is there
another
function I can use to convert the result to a string even if it is
longer than 255?

Cheers
TonyS.


Carim wrote:
Hi Tony,

=INDEX(A1:C5,MATCH(3,C1:C5,FALSE),1)

should do the job ...
HTH
Cheers
Carim