Find the Last Occurrence of a Lookup Value in a List in Excel

Want to find the last occurrence of a lookup Value in a list? In this video, I will show you three ways to do this.
Read More: trumpexcel.com/find-last-occu...
While the VLOOKUP function can find the first matching instance, what if you want to find the last instance of the lookup value.
In that case, you need to use a combination of formulas.
In this video I cover three methods - two of these methods use inbuilt excel functions, and the third one uses VBA to create a custom function.
The first technique uses the fact that the last matching item would have the highest row number (among all the other matching values). It uses a formula to find that row and return its position.
The second method uses the LOOKUP formula to do this.
The third method uses a custom function. Below is the code that is used to create the function:
'This is a code for a function that finds the last occurrence of a lookup value and returns the corresponding value from the specified column
'Code created by Sumit Bansal (trumpexcel.com)
Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
For i = LookupRange.Columns(1).Cells.Count To 1 Step -1
If Lookupvalue = LookupRange.Cells(i, 1) Then
LastItemLookup = LookupRange.Cells(i, ColumnNumber)
Exit Function
End If
Next i
End Function
You can use any of the following methods to get this done.
You can read more about this here: trumpexcel.com/find-last-occu...
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: ruclips.net/user/trumpexc...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

[@user-tf1wo8wo7r]

Fabulous, clearly delineated training! Thank you for sharing your expertise with the rest of the community.

[@molinodeviento8598]

OMG! I have been looking for such formula for over 2 weeks now! I just couldn't come up with a function that would return a Range of values instead of just one value (like VLOOKUP function alone). This is exactly what I was looking for, thank you very much!

[@BUSINESSRESOURCES130]

This is exactly what I needed to find the last date that an item sold in my online store. I have the product ids in one column and sold dates in another. Vlookup only returns the 1st date sold but I needed the last. Thank you so much for providing both functions and VBA solution!

[@EV-olution]

This was perfect for my requirement and it works perfectly once adapted to my list. I used the first method with index. Thank you very much.

[@trumpexcel]

Glad you found the video useful!

[@impex2324]

Thank you Sumit! I searched for a solution for that for a really long time. Great!

[@musacj]

Thank you, I used the second method... with semi-colon as a separator for the function

[@trumpexcel]

Glad you found the video helpful 🙂

[@elifafsar585]

thanks a lot , it is most helpful video for the case, love it !

[@muhammadbasher2466]

very nice tutorial, thank u

[@guillaumefo]

thanks, but how do you leave the F9 key result without deleting the whole formula please ?

[@saigonbaby]

thanks a lot. this was perfect for my work

[@mrsnulch]

Saved my skin again!

[@chinchet32]

Thanks so much brother

[@ExceliAdam]

I like the most LOOKUP solution :)

[@keithchikumbirike]

Thanks a lot.
How can I use this formula combined with the INDIRECT function to return a list of the reference value (from a named ranged where reference is the name of range) and hence create a master appended list from one reference and the next and so on...

[@atulshukal4072]

Very good for start date same date

[@julylahera]

Thanks for you video! do you know how can i do the lookup to get the previous value to the last one? :)

[@michaeldingee743]

This video was valuable as starting point for what I'm trying to. do. I have a report I'm trying to create that looks at a repeating value in a column and returns the adjacent value. My formula is =IFERROR(LOOKUP(2,1/($C:$D=C11),$D:$D),"") . The problem is that the value in the lookup range repats multiple times and the formula returns the last value, not the next repeated range value. Any idea would be helpful.

[@bsahane]

Hello Sir,
Thanks for Great Tutorial !
but How to Find Last Non Empty Cell for same explained in above Tutorial.
Please Help me..

[@puneetvij8433]

Sumit Sir,
How to prepare the EPF statement of employees like a 'salary slip', if I have a database of a financial year and there is duplicacy (like Name, Employee ID) in the database because of monthly EPF entry of each employee. ; )

[@AyuIndayani05]

Hi what if the value I want is not in the list? I want it to show NA or 0 if value is not found in the list.

[@mayur6798]

It is helping me until i don't add new entry of existing names...
Means i hv 5 entries name "X" with final status / Date
but when I'm entering name "X" at 6th time and keeping status / date blank, the results is coming blank for all the "X".
Can you help me to fine the final status of "X"..?

[@excelsavvy02]

Sir,
Can I use it google sheet.?

[@narentechworld2764]

I want this but horizontal way can anyone please clarify. Please

[@paramsingh383]

if we have to take 1st wala to kaise hoga ?

[@caribeiro77]

Another option would be:
=XLOOKUP(D3,A2:A14,B2:B14,"";0;-1)