We all use VLOOKUP and must have observed a shortcoming that it only provides you with the first matching result. I mean if we have data where the lookup_value is coming twice or more times, VLOOKUP will give us the result for the first occurrence only. In the data below if your boss is interested in knowing the commission paid to "Mavericks Reality" for their second booking, how will you find that?
Solution will go like this:
You'll be a adding a helper column right next to broker's names and try to get a unique name. I am using COUNTIF function to do the trick for me like: =COUNTIF($B$2:B2,B2)&B2.
What this will do is, convert our lookup_values as 1Mavericks Reality, 2Mavericks Reality and so on (like in the image below). Now you have a unique lookup_value and you can simply use VLOOKUP("2Mavericks Reality",table_array,col_index) and you are done with your answer.
This is simple right?
You can download the sample file to get a better understanding of this tip. It tells you how you create formulae to get 2nd or 3rd occurrence. VLOOKUP FOR 2nd, 3rd OCCURRENCE.
Share this tip with everyone. "Happy Learning"
This comment has been removed by the author.
ReplyDeleteand if you guys want to use VBA here is the UDF:
ReplyDeleteFunction VLOOKUPNTH(lookup_value, table_array As Range, col_index_num As Integer, nth_value)
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
VLOOKUPNTH = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, 1).Value = lookup_value Then
nVal = nVal + 1
' Check to see if this is the nth match
If nVal = nth_value Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
End If
Next nRow
End With
End Function
Nice one Prakash :)
ReplyDeleteHowever, one suggestion. Instead of looping use the inbuilt .Find() as it is much faster than looping when you have enormous records :)
Keep up the good work :)
Sid