Mrinmoy Roy
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.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
4 Ways to Vlookup and Pull the Last Match in Excel
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:
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)❸ Press the ENTER button.
␥ 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.
Read More: How to Find Case Sensitive Match in Excel ( 6 Formulas)
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))❸ Press the ENTER button.
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.
Read More: How to Return Row Number of a Cell Match in Excel (7 Methods)
Similar Readings
- Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods
- How to Match Names in Excel Where Spelling Differ (8 Methods)
- How to Match Data in Excel from 2 Worksheets
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)❸ Press the ENTER button.
␥ 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.
Read More: Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
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.
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)❸ 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.
Read More: Excel VBA to Match Value in Range (3 Examples)
Things to Remember
📌 Press ALT + F11 to open up the VBA editor.
📌 You can press CTRL + S to save the VBA code.
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. And please visit our website Exceldemy to explore more.
It is a very good Excel website with all the basic and advance level details .Thank you so much .
Thanks for your feedback.
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”?
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!