How to lookup column that is reversed

[ke9c]

Hi, I have below problem:
I want to lookup colA result based on ColB entry, but vlookup can only lookup colB as result based on colA, anyone have any idea how to do that? Thanks

ColA ColB
---- ----
A 123
B 456
C 789

 

[jbeaucaire]

Options:
1) Reverse your two columns so the VLOOKUP will work.

2) If column B is already sorted ascending, a LOOKUP() will still work:

=LOOKUP(1234, B:B, A:A)

3) If neither option above is viable, the use INDEX/MATCH:

=INDEX(A:A, MATCH(1234, B:B, 0))

 

[Aladin Akyurek]

Hi, I have below problem:
I want to lookup colA result based on ColB entry, but vlookup can only lookup colB as result based on colA, anyone have any idea how to do that? Thanks

ColA ColB
---- ----
A 123
B 456
C 789

If A:B is sorted in ascending order on A and a value like 200 is not associated with any value in the above sample:

=IF(LOOKUP(E2,$B$2:$B$4)=E2,LOOKUP(E2,$B$2:$B$4,$A$2:$A$4),"")

If the ascending order set up does not hold:

=INDEX($B$2:$B$4,MATCH(E2,$A$2:$A$4,0))

Note. E2 houses a look up value like 456.

 

[ke9c]

Thanks, it works amazingly, unlike lookup, will have some errors when lookup. Thanks a lot.