vlookup from right to left

[3Suk]

dear all,
in vlookup, the col_index_num must be on the right hand side, what if target column is on the left hand side of the key such as below example.

colA colB colC
row1 A B C
row2 D E F
row3 G H I

in above table, search F in column 3, return me the D.
if use, vlookup, suppose it should be like "=vlookup("F",C:A,-3)
of course, col_index_num less 1 will return an error "#VALUE!"

Please advise
thanks for your help in adv.

Patrick

 

[Claus Busch]

Hi,

Am Thu, 17 Jul 2014 23:19:36 -0700 (PDT) schrieb 3Suk:

colA colB colC
row1 A B C
row2 D E F
row3 G H I

in above table, search F in column 3, return me the D.

try:
=INDEX(A:A,MATCH("F",$C$1:$C$100,0))


Regards
Claus B.

 

[3Suk]

dear all,

in vlookup, the col_index_num must be on the right hand side, what if target column is on the left hand side of the key such as below example.



colA colB colC

row1 A B C

row2 D E F

row3 G H I



in above table, search F in column 3, return me the D.

if use, vlookup, suppose it should be like "=vlookup("F",C:A,-3)

of course, col_index_num less 1 will return an error "#VALUE!"



Please advise

thanks for your help in adv.



Patrick

Claus,
great thanks.
you always give me a hand when i'm in need.

Rgds,
Patrick.