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

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

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

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

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