Using VLOOKUP in Google Sheets is a great way to speed up your productivity when you need to pull data from another range or sheet.
Many VLOOKUP users often wonder if there is a way to VLOOKUP to the left—or in other words—do a reverse VLOOKUP.
Luckily this is possible with a little know-how.
In this tutorial, I will show you how to do a reverse VLOOKUP in Google Sheets.
Table of Contents
Reverse VLOOKUP Syntax
To do a VLOOKUP to the left, we’re going to have to create our own formula, because this functionality is not built-in to the function already.
We just have to make a few adjustments to the regular VLOOKUP formula.
Here will be our formula:
=VLOOKUP(search_key, {lookup_range, return_range}, 2 , [is_sorted])
- search_key – this is the value we are searching for
- lookup_range – this is the range that contains the value we are searching for
- return_range – this is the range that contains the value that will be returned when the search_key is found in the lookup_range
- 2 – this argument will have to be set to “2” for the reverse VLOOKUP to work properly, it just means to return the second column (which is our return_range)
- is_sorted – this is an optional argument that will be set to TRUE by default. This indicates whether the lookup_range is sorted or not. In most cases, you should set this to FALSE
The only difference between this formula and a regular VLOOKUP formula is that we have two ranges (lookup_range, and return range) and the ranges are wrapped in curly brackets.
When we wrap these ranges in curly brackets, it creates a virtual array where the lookup_range is in one column, and the return_range is in a second column next to it.
In a regular VLOOKUP, there is only one range entered into the formula, and you cannot search to the left.
But with this virtual array, we have created using two ranges and curly brackets, we can solve this issue.
Using Our Reverse VLOOKUP Formula
Now that I have gone over the syntax of the formula we will be using, let’s take a look at how to actually use it.
I will be setting up this formula with the below example where I have the product and I want to VLOOKUP the column to the left, which is the “category” the product belongs to.
Here are the steps to set up this formula:
1. First select the cell where you want to do the VLOOK and type “=VLOOKUP”, press Tab on your keyboard, and select the cell that contains the value you are searching for
2. For the next argument of the function, you will use a curly bracket “{” and place the cell range that you are looking in to find your search_key value. In my example, this is $B$2:$B$5
3. Next, place the cell range that you want to return the matching value from when the search_key is found in the lookup_range. In my example, this is $A$2:$B$5. You will then need to close our your curly brackets “}” by adding another one to the formula
4. Then you will place a “2”, a comma, and then “FALSE”. You can now close out your formula with the ending parenthesis
5. When you press Enter on your keyboard you should see that your reverse VLOOKUP formula has worked
6. If you have additional values you need to look up you can copy your formula and paste it down a column to return additional values
Closing Thoughts
Learning to look up to the left is a useful skill for all spreadsheet users to know.
This formula is very useful if you have a large list of data and you need to pull certain values out.
If you are still confused at all about how to set this function up in your sheet, make sure to watch the video!
More Google Sheets Tutorials:
XLOOKUP Alternatives