How to use VLOOKUP to pull the last match in Excel – 3 ways

Here are three easy methods!

The Microsoft Excel logo is displayed against a purple background.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you want to know how to use VLOOKUP to pull the last match in Excel, we’ve got your back.

VLOOKUP’s syntax only returns the first occurrence of the lookup value. However, if you have data where the lookup values have multiple occurrences, the simple VLOOKUP function will not work. To help you out, we have consolidated 4 ways that you can use to use VLOOKUP to return the last occurrence of your lookup value in Excel.

So, without wasting another second, let’s get started!

Using VLOOKUP to fetch the last match in Excel

Scenario on hand: We have a fictional dataset of incoming inventory with dates, product names, and quantity. 

A screenshot of a spreadsheet in Excel.

What we want to accomplish: Explore the ways to use VLOOKUP to fetch the last occurrence of the lookup value:

  1. Using the LOOKUP function
  2. Using the INDEX and MATCH Function 
  3. Using the Last Entry Date

Method 1: Using the LOOKUP function

We want to fetch the last inventory update from the dataset. For this, we can use the LOOKUP function.

Here’s the syntax we will use:

=LOOKUP(2,1/({Column array of lookup value}={Cell address of lookup value}),{Column array of return value})

We use the product column instead of the column array of lookup values. 

We use the inventory column instead of the column array of return values. 

Here’s the formula that it makes:

=LOOKUP(2,1/(C6:C13=G6),D6:D13)

Here’s what we get when we input this formula into our sheet:

A screenshot of a Microsoft Excel spreadsheet with various data entries.

Using this formula, we can get the last value (300) instead of the first occurrence (550). 

Method 2: Using INDEX and MATCH function 

The INDEX and MATCH functions are often used in place of VLOOKUP for complex situations. We can use this combination in our case as well. 

Here’s the syntax of the INDEX and MATCH functions which we will use:

=INDEX({Column array of return value},MATCH(2,1/({Column array of lookup value column}={Cell address of lookup value}),1))

For our data, we change this syntax to:

=INDEX(D6:D13,MATCH(2,1/(C6:C13=G6),1))

This formula yields the result:

A screenshot of an excel spreadsheet with a number in it.

Method 3: Using the last entry date

The last method that you can use to fetch the last entry in the dataset is using the date. 

Using the MAX function, you will first have to fetch the last entry date. 

In our case, it is 1/18/23, as demonstrated below:

A screenshot of a Microsoft Excel spreadsheet containing data from the last match.

To search for both the date and the product name, we need to add a helper column to the left. We simply concatenate the two columns using the following:

={Cell address 1}&{Cell address 2}

Here’s how we apply it to our data:

=B6&C6

Learn how to create a spreadsheet in Excel using these simple steps.

Then, using the Fill Handle tool, we will expand this formula across the table:

A screenshot of an Excel spreadsheet with a list of numbers.

Then, using this VLOOKUP syntax, we look for the last entry of the product “Chips – salted”:

=VLOOKUP({Lookup value 1}&{Lookup value 2},{Table array},{Column index number},TRUE)

We use TRUE in this formula because the “Chips – Salted” product was not necessarily updated on the latest date. 

Here’s the formula we used for our data:

=VLOOKUP(G5&G6,A6:D13,4,TRUE)

Here’s what we get after entering the formula:

A screenshot of an Excel spreadsheet.

Wrapping up

VLOOKUP pulls the first occurrence of a lookup value. However, this tutorial explores how you use VLOOKUP and other methods to pull the last match in Excel. All of these methods are quite easy. 

Learn more about the VLOOKUP function in Excel through these easy-to-follow guides: