How to Vlookup and Pull the Last Match in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Suppose you have an Excel sheet with repetition of the same item couple of times. Now from the list, you want to get the last item from the same types of items. In this circumstance, you may want to search for queries to get the last match throughout your Excel worksheet. In a view of assisting you in this regard, we’ve discussed 4 different ways that you can use to vlookup your worksheet and pull the last match in Excel with ease.


How to Vlookup and Pull the Last Match in Excel: 4 Ways

In this article, we will be using a sample product quantity list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

Dataset: 4 Ways to Vlookup and Pull the Last Match in Excel

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Apply LOOKUP Function to Pull the Last Match in Excel

First and foremost the function we want to use to vlookup through your Excel workbook is the LOOKUP function. You can use this function to vlookup and return the last match among the repetition of the same item multiple times in your Excel workbook with ease. For example, in our dataset the item Monitor repeats 3 times, Keyboard repeats twice and Mouse repeats trice too. Let’s assume that we want to return the quantity for the last occurrence of the item, Monitor. Now, follow the steps below to learn to use this function:

❶ Select cell C15 ▶ to store the last matched result.

❷ Type the formula

=LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12)
within the cell.

❸ Press the ENTER button.

Apply LOOKUP Function to Pull the Last Match in Excel

  Formula Breakdown

  • $B$5:$B$12 ▶ range of cells where to look for the item, Monitor.
  • $B$15 ▶ cell address where the lookup value is stored.
  • $C$5:$C$12 ▶ range of cells from where the match result is returned.
  • =LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12) ▶ pulls out the last matched quantity for the item, Monitor.

2. Utilize INDEX and MATCH Function to Return the Last Match in Excel

In this section, we are going to use the INDEX and MATCH functions to vlookup through our data table and return quantity for the last match item, Mouse. To use the two functions in collaboration with one another to find the last matched quantity for the item, Mouse, follow the steps below:

❶ Select cell C15 ▶ to store the last matched result.

❷ Type the formula

=INDEX(C5:C12,MATCH(2,1/(B5:B12=B15),1))
within the cell.

❸ Press the ENTER button.

Utilize INDEX and MATCH Function to Return the Last Match in Excel

After entering the formula, we got the Mouse quantity as 32 which is the last occurrence quantity for the Mouse item.

  Formula Breakdown

  • B5:B12 ▶ range of the cells where to search for the items.
  • B15 ▶ cell address that holds the lookup value.
  • MATCH(2,1/(B5:B12=B15),1) ▶ searches for the last occurrence of the item Mouse, and returns a numerical value, 8. This means, at the 8th row of the table, the last occurrence of the item, Mouse exists.
  • =INDEX(C5:C12,MATCH(2,1/(B5:B12=B15),1)) ▶ The INDEX function looks for the quantity at the 8th row of the data table, return by the function MATCH.

3. Use VLOOKUP Function to Get the Last Match in Excel

Now, we will find the quantity for the last occurrence of the item Keyboard using the VLOOKUP function. To get to know the usage of the function, follow the steps below:

❶ Select cell C15 ▶ to store the last matched result.

❷ Type the formula

=VLOOKUP(B15,$B$5:$C$12,2,TRUE)
within the cell.

❸ Press the ENTER button.

Use VLOOKUP Function to Get the Last Match in Excel

  Formula Breakdown

  • B15 ▶ holds the lookup value that is Keyboard.
  • $B$5:$C$12 ▶ rang of the table array i.e. the area of the whole table.
  • 2 ▶ represents the column index number i.e. from column no. 2 the match result is returned.
  • =VLOOKUP(B15,$B$5:$C$12,2,TRUE) ▶ returns the quantity of the Keyboard for its last occurrence.

4. Employ VBA Code to Catch the Last Match in Excel

Now we will create a user-defined function called VlookupLastMatch using VBA code. You can use this function to Vlookup through your data table and return the last match in Excel. Now follow the steps below to learn to use this code:

❶ Press ALT + F11 to open up the VBA editor.

❷ Go to Insert Module to create a new module.

❸ After the copy the following VBA code:

Function VlookupLastMatch(AA As String, BB As Range, CC As Integer)

Dim i As Long

For i = BB.Columns(1).Cells.Count To 1 Step -1

If AA = BB.Cells(i, 1) Then

VlookupLastMatch = BB.Cells(i, CC)

Exit Function

End If

Next i

End Function

❹ Now paste this code onto the VBA editor and save the code.

Employ VBA Code to Catch the Last Match in Excel

After that return to your Excel worksheet and

❶ Select cell C15 ▶ to store the last matched result.

❷ Type the formula

=VlookupLastMatch(B15,B5:C12,2)
within the cell.

❸ Press the ENTER button.

  Formula Breakdown

  • B15 ▶ cell address that contains the lookup value.
  • B5:C12 ▶ range of the table array.
  • 2 ▶ column index number to return the last match result.
  • =VlookupLastMatch(B15,B5:C12,2) ▶ returns for quantity for the lastly matched item, Monitor.

Things to Remember

📌 Press ALT + F11 to open up the VBA editor.

📌 You can press CTRL + S to save the VBA code.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


Conclusion

To sum up, we have discussed 4 methods to Vlookup and pull the last match in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.


<< Go Back to | Excel Match | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

5 Comments
  1. It is a very good Excel website with all the basic and advance level details .Thank you so much .

  2. Thanks for your feedback.

  3. Hello, is there a way to have it return a different value if the lookup value cannot be found. For example, if the product equal stand, how can I make it return “Null”?

    • Reply
      Konstantinos Theodorou Feb 8, 2023 at 7:32 PM

      Hi Sam

      I believe you should try xlookup function it is much more flexible than vlookup.

      And yes it gives you the option o f value in case that it can’t find a match

      Regards
      Konstantinos

    • Hi SAM,
      The first formula: =LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12) returns #N/A error for a lookup value that cannot be found. Thus, you can add the IFERROR function to tackle this issue.
      For example use the following formula to return “Null” instead of #N/A error: =IFERROR(LOOKUP(2,1/($B$5:$B$12=$B$15),$C$5:$C$12),”Null”)
      I hope this is what you were looking for.
      Regards!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo