In this tutorial, you will learn how to do a reverse vlookup in Google Sheets.
How To Do a Reverse Vlookup in Google Sheets
Quick Navigation
Reverse VLOOKUPs can be used in a variety of situations where you need to find data in a table when you only have a partial match.
This can be useful when you have a large table of data and you need to find a specific piece of information.
For example, if you have a table of data that includes the names of countries in the second column and the population in the third column, you can use a reverse VLOOKUP to find the population of a specific country.
Since the VLOOKUP function cannot be used for matching values that are not in the first column, we’ll have to use a different set of functions.
How To Perform a Reverse Vlookup in Google Sheets (in 6 steps)
Here’s how to perform a reverse vlookup on your current spreadsheet:
Step 1
Select the cell that will hold the reverse lookup formula.
Step 2
Use the INDEX formula to select a range to output.
The first argument of the INDEX formula must be the range that corresponds to the field you want the formula to return.
In this example, we selected the range B4:B8 since we want to return the price given a product ID.
Step 3
Use the MATCH function to determine the offset required to return the appropriate price.
Select the string you would like to match as the first argument of MATCH.
The second argument must be the cell range where you want Google Sheets to look for a match.
Set the third argument of MATCH to ‘0’ to indicate that we are looking for an exact match.
The output of the MATCH function will be used as the second argument to our INDEX function.
For example, if our lookup string is found in the first row of the Product ID field, the INDEX function should return the price in the first row of the Price field.
Step 4
Hit the Enter key to evaluate the function.
Step 5
You may need to convert your cell references to absolute references to prevent the ranges from changing.
Step 6
You may choose to return a blank string or a helpful message such as “Invalid ID”.
The final formula should now be:
=IFNA(INDEX($B$4:$B$8,MATCH(B13,$C$4:$C$8,0)),”Invalid ID”)
Summary
This guide should be useful for users who want to perform a reverse vlookup in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.