Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to VLOOKUP to the left

vlookup how to lookup left

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:

vlookup left example worksheet

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.

Advanced VLOOKUP Cheat Sheet

Download Icon
Download the file: Advanced VLOOKUP Cheat Sheet

Other posts in the Mastering VLOOKUP Series


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *