User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
 
VLOOKUP formula that looks Left instead of Right
 
Related Links:
1. Excel VLOOKP Function, with examples.
2. Left Lookup, with Index, Match & Offset Excel functions
3. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.
4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value.
 

 
 
 
Vlookup B1 in Column A
 
=VLOOKUP(B1,CHOOSE({1,2},B1:B5,A1:A5),2,0)  [Formula]
 
The formula returns the string "Jack". (Refer Table 1)
 
Formula does a Vlookup on B1, choosing Column B as first column and Column A as second column, in the table_array.
 
Returns matching value from col_index_num 2 (ie. column A), in table_array.
 
=CHOOSE({1,2},B1:B5,A1:A5)  [Formula Break]
 
This part of the formula, chooses columns and rearranges their order in table_array: (B1:B5,A1:A5).
 
-----------------------------------------------------------------------------------------------------------------------------
 
 
 
Vlookup C3 in Column A
 
=VLOOKUP(C3,CHOOSE({1,2},C1:C5,A1:A5),2,0)  [Formula]
 
The formula returns the string "Romeo". (Refer Table 2)
 
Formula does a Vlookup on C3, choosing Column C as first column and Column A as the second column.
 
Returns matching value from col_index_num 2 (ie. column A), in the table_array.
 
=CHOOSE({1,2},C1:C5,A1:A5)  [Formula Break]
 
This part of the formula, chooses columns and rearranges their order in table_array: (C1:C5,A1:A5).
 
-----------------------------------------------------------------------------------------------------------------------------
 
 
 
Vlookup C3 in Column B
 
=VLOOKUP(C3,CHOOSE({2,3,4},D1:D5,C1:C5,B1:B5,A1:A5),2,0)  [Formula]
 
The formula returns the value 44. (Refer Table 3)
 
The formula does a Vlookup on C3, choosing Column C as first column, Column B as second column and Column A as third column.
 
Returns matching value from col_index_num 2 (ie. column B), in the table_array.
 
=CHOOSE({2,3,4},D1:D5,C1:C5,B1:B5,A1:A5)  [Formula Break]
 
This part of the formula, chooses columns and rearranges their order in table_array: (C1:C5,B1:B5,A1:A5).
 
-----------------------------------------------------------------------------------------------------------------------------
 
 

Vlookup B2 in Column A
 
=VLOOKUP(B2,CHOOSE({3,1,4},D1:D5,C1:C5,B1:B5,A1:A5),3,0)  [Formula]
 
The formula returns the string "Jill". (Refer Table 4)
 
The formula does a Vlookup on B2, choosing Column B as first column, Column D as second column and Column A as third column.
 
Returns matching value from col_index_num 3 (ie. column A), in the table_array.
 
=CHOOSE({3,1,4},D1:D5,C1:C5,B1:B5,A1:A5)  [Formula Break]
 
This part of the formula, chooses columns and rearranges their order in table_array: (B1:B5,D1:D5,A1:A5).