User Rating: 4 / 5     VLOOKUP formula that looks Left instead of Right

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).