How do I use VLOOKUP in Google Sheets to return the last matching value?

By /

VLOOKUP is a function in Google Sheets that allows users to search for a specific value in a table or range and return a corresponding value from a different column. To return the last matching value using VLOOKUP, the user must first sort the data in the table in descending order. This ensures that the last matching value is at the top of the table. Then, the user can use the VLOOKUP function and specify the range to search for the value, set the search mode to “FALSE” to find an exact match, and set the column index to “0” to return the last matching value. This will return the last value that matches the specified search criteria.

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

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

x

Related terms:

  1. How can I use VLOOKUP in Google Sheets to return the last matching value?
  2. How can VLOOKUP be used in Google Sheets to return the minimum value?
  3. How can VLOOKUP be used to return the maximum value in Google Sheets?
  4. Can I use VLOOKUP to return multiple columns in Google Sheets?
  5. How to Use VLOOKUP to Return Value in Next Cell in Excel?
  6. Can Google Sheets search for a value in a list and return a Yes or No answer?
  7. How Can I Use VLOOKUP in Google Sheets to Find a Value That Falls Within a Range?
  8. How can I use Google Sheets to return multiple values based on a single criteria?
  9. Excel: Use VLOOKUP to Return Multiple Values in One Cell
  10. Excel: Use VLOOKUP to Return Multiple Values Vertically