VLOOKUP is a very useful formula; most advanced Excel users still encounter situations where it would be a suitable solution. But a basic VLOOKUP has one major flaw; it cannot lookup a value to the left. The value being looked up must always be in the first column and the value returned must always be to the right. But data is not always provided like that, sometimes the data you wish to lookup is on the left – so what can you do?
I’ve got three options for you.
- Create a duplicate column
- Use the INDEX/MATCH functions
- Use the VLOOKUP & CHOOSE functions together
The examples below use the following table:
This is a very simple example with countries (Column B) and the continents they are in (Column A). The challenge is how do we find out the name of a continent based on the country name?
Option 1 – Create a duplicate column
This first option is not sophisticated and would make some Excel users shake their heads in disgust. But we could just duplicate Column A by copying it into Column C, then do a standard VLOOKUP. This solution would work perfectly well.
=VLOOKUP("Indonesia",B2:C19,2,FALSE)
This formula would return Asia as the result.
There are many ways in which this is not best practice, but sometimes we just do not need a complicated solution. In these circumstances this solution will work perfectly well.
Option 2 – Use the INDEX/MATCH functions
We can use the INDEX/MATCH functions. This is a combination of two separate functions, INDEX and MATCH. Let’s briefly look at each of these.
MATCH
The MATCH function will return a number indicating where in a list a specific value has been found. So, if we were to use a MATCH formula to find Indonesia in Cells B2-B19, it would return 5, as it is the 5th row in that range.
The format of the match formula is:
=MATCH(lookup_value, lookup_array, match_type)
The MATCH function is similar to VLOOKUP, but it does not require a column index number. It will only lookup in the list of cells contained in the lookup-array and will only return the position number.
MATCH has the match_type criteria, this is similar to VLOOKUP. We will want to use 0 in most circumstances to create an exact match.
INDEX
The INDEX formula returns a cell value based on the position number of a given range.
Having used MATCH to find that Indonesia is in the 5th row, we can use the INDEX formula to find the 5th item in the range.
=INDEX(A2:A19,5)
The INDEX formula would return Asia as a result.
Combining INDEX and MATCH
By combining INDEX and MATCH it is equivalent to a VLOOKUP that can lookup to the right or the left.
It is possible to combine INDEX/MATCH into a single formula; it is not necessary to calculate the functions in separate columns. Using our example, the full formula would be as follows:
=INDEX(A2:A19,MATCH("Indonesia",B2:B19,0))
If this is your first introduction to INDEX/MATCH I recommend you take time to learn more about how it works, as it is a very powerful formula combination, which can do much, much more than just replace VLOOKUP.
Option 3 – Use the VLOOKUP & CHOOSE functions together
This is by far the most complicated option, and the hardest to understand. But when combined with the CHOOSE function it is possible for VLOOKUP to look in any direction. The combined VLOOKUP and CHOOSE formula would look like this:
=VLOOKUP("Indonesia",CHOOSE({1,2},B2:B19,A2:A19),2,FALSE)
Let’s break down the CHOOSE function to understand what is happening:
=VLOOKUP("Indonesia",CHOOSE({1,2},B2:B19,A2:A19),2,FALSE)
The CHOOSE function in this situation is defining in which column is which. The numbers in the {} represent the column numbers and the ranges after the {} represent the cell ranges to which they relate. The red highlighted sections below shows that column 1 is defined as cells B2-B19.
=VLOOKUP("Indonesia",CHOOSE({1,2},B2:B19,A2:A19),2,FALSE)
The red highlighted sections below now show that column 2 is defined as cells A2-A19.
=VLOOKUP("Indonesia",CHOOSE({1,2},B2:B19,A2:A19),2,FALSE)
We can add as many columns as we like, but each number within the {} must have a corresponding range within the CHOOSE formula. Also, the numbers within the {} must be sequential. As an example, if we had 3 columns we could set out our formula as follows:
=VLOOKUP([lookup_value],CHOOSE({1,2,3},[lookup_range1],[lookup_range2], [lookup_range_3]),3,FALSE)
Conclusion
There are many ways to achieve a lookup to the left. Depending on your Excel skill level and the complexity of your situation you can pick a solution which will work for you. Many Excel users believe that VLOOKUP cannot lookup to the left, but now you know that when combined with the CHOOSE function that it is possible.
Download the Advanced VLOOKUP Cheat Sheet
Download the Advanced VLOOKUP Cheat Sheet. It includes most of the tips and tricks we’ve covered in this series, including faster calculations, multiple criteria, left lookup and much more.
Please download it and pin it up at work, you can even forward it onto your friends and co-workers.
Other posts in the Mastering VLOOKUP Series
- How to use VLOOKUP
- VLOOKUP: What does the True/False statement do?
- VLOOKUP: How to calculate faster
- How to VLOOKUP to the left
- VLOOKUP: Change the column number automatically
- VLOOKUP with multiple criteria
- Using wildcards with VLOOKUP
- How to use VLOOKUP with columns and rows
- Automatically expand the VLOOKUP data range
- VLOOKUP: Lookup the nth item (without helper columns)
- VLOOKUP: List all the matching items
- Advanced VLOOKUP Cheat Sheet
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.