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

2 Replies to “Google Sheets: Use VLOOKUP to Return Last Matching Value”

  1. Hi Zach,

    Thank you for your formula. I have been looking for a formula like this one.

    However, the last matching value is blank and I want it to find the last matching non-blank value? Are you able to help me tweek this formula?

    Many Thanks

    1. Hi Omar…Sure! To find the last matching non-blank value using VLOOKUP in Google Sheets, you’ll need to use a combination of functions since VLOOKUP by itself won’t directly return the last matching non-blank value. Here’s how you can do it:

      ### Step-by-Step Approach

      1. **Use FILTER** to find all matching values.
      2. **Remove blanks** from the filtered results.
      3. **Get the last non-blank value**.

      Here’s a formula that accomplishes this:

      “`excel
      =INDEX(FILTER(B:B, A:A = “criteria”, B:B <> “”), COUNTA(FILTER(B:B, A:A = “criteria”, B:B <> “”)))
      “`

      ### Explanation:
      – **FILTER(B:B, A:A = “criteria”, B:B <> “”)**: Filters column B to find values where column A matches the criteria and column B is not blank.
      – **COUNTA(FILTER(B:B, A:A = “criteria”, B:B <> “”))**: Counts the number of non-blank filtered values.
      – **INDEX**: Uses the count of non-blank filtered values to get the last non-blank value.

      ### Example:
      Suppose you have a data range `A2:B10`, and you want to find the last non-blank value in column B where column A matches “criteria”.

      “`excel
      =INDEX(FILTER(B2:B10, A2:A10 = “criteria”, B2:B10 <> “”), COUNTA(FILTER(B2:B10, A2:A10 = “criteria”, B2:B10 <> “”)))
      “`

      This formula will return the last non-blank value in column B where column A matches “criteria”.

      Feel free to adjust the range (`B2:B10` and `A2:A10`) to match your specific data set.

Leave a Reply

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