# How to VLOOKUP Last Match in Google Sheets (5 Simple Ways)

The VLOOKUP is one of the handiest operations in Google Sheets and Excel. This operation is performed we need to search or extract information from a large dataset. There are several functions like the VLOOKUP, XLOOKUP, or a combination of INDEX and MATCH functions, etc. which can perform the VLOOKUP operation. However, if a dataset contains several matches for any specific value, then the usual VLOOKUP operation will return only the first match. So, is there any way to return the last match? The answer is a resounding Yes and in this article, I’ll discuss 5 simple ways that will help you to VLOOKUP the last match in Google Sheets.

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the described ways to VLOOKUP the last match in Google Sheets.

## 5 Simple Ways to VLOOKUP Last Match in Google Sheets

First, let’s get familiar with our dataset. The dataset contains the list of English Premier League winning teams for the last few seasons and their points at the end of that season. We will use a winning team name as a lookup value and return the points for the last match. Now keep reading the following methods.

### 1. Uniting INDEX and MATCH Functions

A simple way to VLOOKUP the last match in Google Sheets is to unite the INDEX and MATCH functions. Although, we will use the arguments of the MATCH function a bit differently to extract the last match.

Steps:

• First, select Cell C15.
• Afterward, type in the following formula:
`=INDEX(D5:D12,MATCH(28,1/(\$C\$5:\$C\$12=\$C\$14),1))`
• Finally, press Enter key to get the required result.

Formula Breakdown

• MATCH(28,1/(\$C\$5:\$C\$12=\$C\$14),1)

First, the MATCH function searches for a random value 28 in the search range. But a match will not be found since the provided range 1/(\$C\$5:\$C\$12=\$C\$14) will return either 1 or #DIV/0! error. Therefore, the MATCH function will return the relative position of the last non-error cell and this will help the INDEX function to return the last match.

• INDEX(D5:D12,MATCH(28,1/(\$C\$5:\$C\$12=\$C\$14),1))

Then,  from range D5:D12, the INDEX function returns the cell’s content with an index returned by the MATCH function.

### 2. Combining INDEX, MAX, FILTER, and ROW Functions

Another way to return the last match from a dataset is to combine the INDEX, MAX, FILTER, and ROW functions.

Steps:

• Select Cell C15
• After that, type in the following formula:
`=INDEX(C:D,MAX(FILTER(ROW(C5:C12),C5:C12=C14)),2)`
• Then, press Enter key to get the required result.

Formula Breakdown

• ROW(C5:C12)

First, the ROW function returns the row number for each cell within the range C5:C12.

• FILTER(ROW(C5:C12),C5:C12=C14)

Afterward, the FILTER function returns the index of the rows where the condition specified by C5:C12 = C14 is true.

• MAX(FILTER(ROW(C5:C12),C5:C12=C14))

Then, the MAX function returns the maximum row number (which is in the last match) in the dataset returned by the FILTER function.

• INDEX(C:D,MAX(FILTER(ROW(C5:C12),C5:C12=C14)),2)

Finally, the INDEX function returns the cell’s content with an index returned by the MAX function.

### 3. Joining ARRAYFORMULA and LOOKUP Functions

The LOOKUP function can also find the last occurrence of a string in a dataset. Since the LOOKUP function is a non-array function, we have to join the ARRAYFORMULA function with it.

Steps:

• Afterward, type in the following formula:
`=ARRAYFORMULA(LOOKUP(28,1/(\$C\$5:\$C\$12=\$C\$14),\$D\$5:\$D\$12))`
• Finally, press Enter key to get the required result.

Formula Breakdown

• LOOKUP(28,1/(\$C\$5:\$C\$12=\$C\$14),\$D\$5:\$D\$12)

Here, the LOOKUP function searches for a random value of 28 in the search range. But a match will not be available since the given range 1/(\$C\$5:\$C\$12=\$C\$14) will return either 1 or #DIV/0! error. Therefore, the LOOKUP function will return the relative position of the last non-error cell and this will help the INDEX function to return the last match.

• ARRAYFORMULA(LOOKUP(28,1/(\$C\$5:\$C\$12=\$C\$14),\$D\$5:\$D\$12))

The LOOKUP function is a non-array function. Therefore, the ARRAYFORMULA function helps the LOOKUP function deal with arrays.

### 4. Merging LOOKUP and SORT Functions

Merging a LOOKUP function and two SORT functions can also help you to VLOOKUP the last match in Google Sheets.

Steps:

• First, select Cell C15.
• After that, type in the following formula:
`=LOOKUP(C14,SORT(C5:C12),SORT(D5:D12,C5:C12,true))`
• In the end, press Enter key to get the required result.

Formula Breakdown

• SORT(C5:C12)

To start with, the first SORT function sorts the search range C5:C12.

• SORT(D5:D12,C5:C12,true)

Then, the second SORT function sorts the result range D5:D12 considering C5:C12 already sorted.

• LOOKUP(C14,SORT(C5:C12),SORT(D5:D12,C5:C12,true))

Finally, the LOOKUP function returns the last match cell’s content for Cell C14.

### 5. Using Apps Script

You can also write a simple script to VLOOKUP the last match in Google Sheets. To execute our script properly, we have modified our dataset.

Steps:

• First, go to the Extensions ribbon and select Apps Script from the options.

• A new window like the following will open in your browser.

• Rename the project and enter the following script:
```function vlookupLastMatch() {
var last_row = ss.getLastRow();
var search_key = ss.getRange(4,7).getValue();
for(var i = 5;i<=last_row;i++){
var match_key = ss.getRange(i,3).getValue();
if (match_key==search_key){
var match_result = ss.getRange(i,4).getValue();
}
}
ss.getRange(5,7).setValue(match_result);
}```

• After that, Save and Run the project. Provide the required permissions.

• The last match will be updated in the required cell.

## Things to Be Considered

• Remember to fix the required cells while using method 1 and method 3.
• The LOOKUP function is not an array function. Therefore, join it with the ARRAYFORMULA function if you need to deal with an array while using the LOOKUP
• Provide required permissions to run the script while using Apps Script.
• Turn on the Iterative Calculation option from the File ribbon if required.

## Conclusion

This concludes our article to learn how to VLOOKUP the last match in Google Sheets. I hope the article was sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more useful videos.

## Related Articles

#### Seemanto Saha

Hello, I am Seemanto Saha, and working as a Technical Writer and Content Creator at OfficeWheel.com. I completed my B.Sc. in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. Being an enthusiast for solving analytical problems, I aim to leverage my research and analytical skills to create better content for everyone.

We will be happy to hear your thoughts