Table of Contents
VLOOKUP is a useful function in Google Sheets that allows you to search for a specific value in a column and return a corresponding value from a different column. To use VLOOKUP to return the last matching value, you can add the SORT function to your formula. This will sort the data in descending order and then the VLOOKUP function will return the last matching value from the sorted data. This can be helpful when you have multiple instances of the same value and you only want to retrieve the most recent or last one.
By default, the VLOOKUP function in Google Sheets looks up some value in a range and returns a corresponding value for the first match.
However, sometimes you may want to look up a value in a range and return the corresponding value for the last match.
You can use the following syntax to do so:
=VLOOKUP(E2, SORT(A2:C11, ROW(A2:A11), FALSE), 3, FALSE)
This particular formula looks up the value in cell E2 within the range A2:C11 and returns the corresponding value from column 3 of the range for the last matching value.
The following example shows how to use this formula in practice.
Example: Use VLOOKUP to Return Last Matching Valuein Google Sheets
Suppose we have the following dataset that contains information about various basketball players:
Suppose we would like to look up the name “Nets” in the Team column and return the last matching value in the Points column.
We can type the following formula into cell F2 to do so:
=VLOOKUP(E2, SORT(A2:C11, ROW(A2:A11), FALSE), 3, FALSE)
The following screenshot shows how to use this formula in practice:
The formula correctly returns the value 17, which is the last matching value in the Points column among all rows that contain Nets in the Team column:
How This Formula Works
=VLOOKUP(E2, SORT(A2:C11, ROW(A2:A11), FALSE), 3, FALSE)
Here is how this formula works:
First, we used SORT(A2:C11, ROW(A2:A11), FALSE) to sort the range A2:C11 based on the row numbers in descending order.
This reversed all of the rows in the original dataset.
Next, we used the VLOOKUP function to look up the value in cell E2 (which was “Nets”) in this newly sorted range, which returned the last matching value in the Points column from the original range.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: