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**

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

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

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.