Functions: VLOOKUP - Left lookup

Hi again!! We saw about VLOOKUP function in last post. We also saw that it works to find a value which is in the first column of table array or towards right from it. So VLOOKUP fails to lookup a value to the left. However there are some cases where you have to apply lookup but have required value to the left side of lookup_value position in table_array.  You do not have permission to change the column sequence or simply do not want to do that. In this case your hope is LOOKUP or a tweaked version of VLOOKUP.

In this post I will show you a way to tweak the standard function and make it work this way. Let's see a quick recap of how VLOOKUP works.

VLOOKUP function looks for a value (lookup_value) in a set of rows and columns (table_array) and returns a value from the same row where lookup_value has been found but from a different column we ask for (col_index_num). You can also request an exact match or an approximate match (range_lookup). Looking closely at table_array description, we can notice that this argument supports the following; 

1. a range reference (like $A$3:$A$17) or 
2. a named range (example: if you have a table named as MyTable, you can simply type MyTable as the second argument instead of range) or
3. a formula that results in the range reference

What we used in our VLOOKUP example in earlier post is the range reference. To lookup a value to the left we will use 3rd property above that tricks VLOOKUP to believe the given array is on the right side where as in reality it will be towards the left. Let's see one example.

I have a list of asset codes along with asset names in a data base. As you see in the below picture, asset code is the first column and next column (to the right) is asset name.

Given the asset code, I can easily use VLOOKUP to retrieve asset name. But if I have asset name given and have to find out asset code which is on the left, VLOOKUP gives up. See the below formula.

Since VLOOKUP always look for lookup_value in the first column and in our example it will not be able to find a match, it results in to an error (#N/A). So what to do now? In this particular example you can use the flexibility of LOOKUP.

But if you want to use VLOOKUP, let’s see how to tweak this function to work in these cases. Enter the below formula and see how it works, below I will explain what we did.

You can cross check the results manually from data base to see results are in fact correct. Once you made sure we are good with the formula, we will now analyse the above function.

We just followed VLOOKUP syntax as required. As we know that VLOOKUP finds a value in the table_array provided and return corresponding value from a chosen column , we have provided table_array that is created by CHOOSE function by combining column B first and then column A. This tricks VLOOKUP to believe column B is indeed first column and column A is second. We will slow down now and analyse what CHOOSE function is doing, bit by bit.

Let’s have a closure look on how we use CHOOSE and what it means. We used the below as an argument in VLOOKUP above.

CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)

We are giving 2 ranges to CHOOSE function which is B1 to B11 and A1 to A11 (last part of the function) and asking it to combine them. Notice that we are giving column B first, then column A and we asked it to combine first one and second one. VLOOKUP considers the combined range for second argument instead of our initial database.That’s the trick. You may get a doubt now. Can we give column A first, then column B and ask CHOOSE to combine 2^nd one first and 1^st one next? Answer is YES. Below formula also works perfect.

CHOOSE({2,1},$A$1:$A$11, $B$1:$B$11)

Essentially, both of above syntaxes creates an array with Column B and Column A. Try these steps to see it yourself. We use CHOOSE as an array function below.

1. Select a 11 rows * 2 columns range.
2.  Type / paste the function “= CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)” without quotes
3.  Press Ctrl+Shift+Enter

Now you can see Excel filled up the selected range like below.

Notice that Asset name is in first column and Asset code later. Our original database is other way around. Once the CHOOSE function does this with in the Excel memory, VLOOKUP does the remaining job of finding and returning the value which is actually to the left of the table.

You can add any number of columns like this to CHOOSE function and extend this function to suite your need. For example, if you have 3 columns in your data (A, B and C) and you want to create an array with  an order B, C & A, just use CHOOSE like below.

CHOOSE({2,3,1} ,$A$1:$A$11, $B$1:$B$11, $C$1:$C$11)

That’s it guys! This way you can make VLOOKUP work with the help of CHOOSE function to return left side value. Post a comment if anything seems confusing above. See you next time!