pivotronics

We all now know that VLOOKUP has one major constraint that the column in the lookup table from where the lookup value is to be searched should be the first column in the lookup table.

Here is a tricky way that you can use to overcome this constraint. The trick is, if we can build the table_array argument (i.e., the lookup table) of the function at runtime in a way ensuring that the first column is the column in the lookup table that is to be searched for the lookup value, the problem is resolved. This is achieved using CHOOSE function inside VLOOKUP in the place of table_array argument to build the lookup table in desired manner at runtime.

Example: Lets understand this with an example. Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

Solution: Here is the VLOOKUP function with CHOOSE function in place of lookup table to return the right values entered in cell F36 to lookup the value in cell F35 and search it in the range B36:C36 (obviously not in the first column) and return the phone number. see the below screenshot:

=VLOOKUP(F35,CHOOSE({1,2}, C36:C46, B36:B46), 2,FALSE)

Explanation:

First argument to the function:  WHAT to search/Lookup: cell F35 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: Here is the CHOOISE function is used as follows: CHOOSE({1,2}, C36:C46, B36:B46). Here the first argument {1,2} will arrange the arrays referred in 2nd and 3rd parameters in that order (i.e., the array referred in 3rd argument of CHOOSE function – C36:C46 comes first and then comes the array referred in 4th argument of CHOOSE function – B36:B46) and returns it as a range/lookup table. so when this happens, the returned lookup table will have C46:C36 as first column that has customer names where the lookup value from cell F35 will be searched, and B36:B46 as second column that has the phone number to be returned.

3rd argument to the function – WHICH column in the lookup table, returned at runtime, enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned ? Here phone number to be returned is in column in the 2nd position (i.e., B36:B46) in the lookup table returned at runtime.

4th argument to the function – How to search. Here we only want to perform exact search. So, argument passed here is 0(zero) or FALSE.

Testing:

Now in the screenshot above, we already have a customer name in the cell F35 – “rathod”, so the value returned should be: 7174683825. When you hit enter “after” key after entering the formula in cell F36, you would see the correct value being returned as in the below screenshot.

Hope you enjoyed reading this post..!!

Want to learn more VLOOKUP tricks..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right. Happy Learning.. 🙂

Do leave your comments, if any..