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: kzitem.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Негізгі бет Find the Last Occurrence of a Lookup Value in a List in Excel
Пікірлер: 27