VLOOKUP to fetch 2nd, 3rd or 4th Value

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"

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. and if you guys want to use VBA here is the UDF:

    Function 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

    ReplyDelete
  3. Nice one Prakash :)

    However, 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

    ReplyDelete