Excel 2002: Can Vlookup search at left hand side of the table?
 

Dear Sir,

Is there a way to lookup for data at the left hand side of the search value?

I try using =VLOOKUP (A1,C$20:D22,-2,FALSE) as illustrated below, but it
does not work.

A B
Lookup value Data (Answer)
1 ND000236 =VLOOKUP (A1,C$20:D$22,-2,FALSE) Answer : PKHLK050
2 ND000237


Look up table

A B C D
20 PKHLK050 05/04/2007 ND000236 (155.00)
21 PKHLB390 18/04/2007 ND000237 200.00
22 PKHLT041 18/04/2007 ND000238 50.00

Is there any other formulas for this function , other than relocating column
A at the right hand side of column C and applying the formula in the usual
way ? (i.e. column index number must be = 1)


Thanks


Low










--
A36B58K641

Excel 2002: Can Vlookup search at left hand side of the table?
 

Think it's easier to use INDEX/MATCH here ..

Try in B1:
=INDEX($A$20:$A$22,MATCH(A1,$C$20:$C$22,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote:
Dear Sir,

Is there a way to lookup for data at the left hand side of the search value?

I try using =VLOOKUP (A1,C$20:D22,-2,FALSE) as illustrated below, but it
does not work.

A B
Lookup value Data (Answer)
1 ND000236 =VLOOKUP (A1,C$20:D$22,-2,FALSE) Answer : PKHLK050
2 ND000237


Look up table

A B C D
20 PKHLK050 05/04/2007 ND000236 (155.00)
21 PKHLB390 18/04/2007 ND000237 200.00
22 PKHLT041 18/04/2007 ND000238 50.00

Is there any other formulas for this function , other than relocating column
A at the right hand side of column C and applying the formula in the usual
way ? (i.e. column index number must be = 1)


Thanks


Low

--
A36B58K641

Excel 2002: Can Vlookup search at left hand side of the table?
 

Hello Max,

Thanks for the formula.

Low


--
A36B58K641


"Max" wrote:

Think it's easier to use INDEX/MATCH here ..

Try in B1:
=INDEX($A$20:$A$22,MATCH(A1,$C$20:$C$22,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote:
Dear Sir,

Is there a way to lookup for data at the left hand side of the search value?

I try using =VLOOKUP (A1,C$20:D22,-2,FALSE) as illustrated below, but it
does not work.

A B
Lookup value Data (Answer)
1 ND000236 =VLOOKUP (A1,C$20:D$22,-2,FALSE) Answer : PKHLK050
2 ND000237


Look up table

A B C D
20 PKHLK050 05/04/2007 ND000236 (155.00)
21 PKHLB390 18/04/2007 ND000237 200.00
22 PKHLT041 18/04/2007 ND000238 50.00

Is there any other formulas for this function , other than relocating column
A at the right hand side of column C and applying the formula in the usual
way ? (i.e. column index number must be = 1)


Thanks


Low

--
A36B58K641

Excel 2002: Can Vlookup search at left hand side of the table?
 

Welcome, Low.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote in message
...
Hello Max,
Thanks for the formula.
Low