Google Sheets: Use VLOOKUP to Return Last Matching Value


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:

Google Sheets VLOOKUP last match

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

Recall the formula that we used to look up the name “Nets” in the Team column and return the last matching value in the Points column:

=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:

Google Sheets: How to Use VLOOKUP to Sum Multiple Rows
Google Sheets: How to Use VLOOKUP with COUNTIF
Google Sheets: How to Use VLOOKUP with IF Statement

Leave a Reply

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