Excel Vlookup second matched value or specific occurrence

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence.

You may also want to read:

Case sensitive lookup

Excel Lookup Function lookup multiple criteria not in the first column

Excel lookup partial text using Vlookup with Wildcard

Excel lookup Function lookup multiple criteria

Excel Vlookup second matched value or specific occurrence

In traditional Vlookup, you are allowed to vertically lookup the first value that matched the criteria (first occurrence), but you cannot lookup the second value, third value, forth value, etc.

In view of this issue, I wrote a custom VBA Excel Function that is capable of vlookup the second value, third value, or any value that you specify (the Nth occurrence).

Syntax of Vlookup

Before we move on, lets recap the syntax of Vlookup

Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )
lookup_value The value to search in the first column of the table or range
table_array The range of cells that contains the data
col_index_num The column number in the table_array argument from which the matching value must be returned
range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match

Syntax of custom lookup Function (wlookup)

The syntax of wlookup is basically same as Vlookup, except that I replace the last parameter to Nth occurance.

wlookup ( lookup_value, table_array, col_index_num, Nth occurance )

lookup_value The Range to lookup, do not accept value in Text
table_array The range of cells that contains the data
col_index_num The column number in the table_array argument from which the matching value must be returned
Nth occurance The Nth occurrence of the lookup

This Function returns “no match” instead of #N/A if no result is found.

VBA code of wlookup – Vlookup second or specific occurrence

Please note that I have not handled much exceptions in the Function, and I do not guarantee this Function is flawless.

Press ALT+F11, copy and paste the below bode in a Module. Note that I have written three Functions.

Public Function wlookup(lookupValRng As Range, tblRng As Range, lookupCol As Integer, occur As Integer) As String
    tblRngFullAdd = tblRng.Address(External:=True)
    WorkBookNm = Replace(Split(tblRngFullAdd, "]")(0), "[", "")
    WorkSheetNm = Split(Right(tblRngFullAdd, Len(tblRngFullAdd) - InStr(1, tblRngFullAdd, "]")), "!")(0)
    tblRngAdd = tblRng.Address
    tblStRng = Left(tblRngAdd, InStr(1, tblRngAdd, ":") - 1)
    tblStColNm = Split(tblStRng, "$")(1)
    tblStColNum = ColNum(tblStColNm)
    tblStRow = Split(tblStRng, "$")(2)
    tblEdRng = Right(tblRngAdd, Len(tblRngAdd) - InStr(1, tblRngAdd, ":"))
    tblEdColNm = Split(tblEdRng, "$")(1)
    tblEdColNum = ColNum(tblEdColNm)
    tblEdRow = Split(tblEdRng, "$")(2)
    TrgColNum = tblStColNum + lookupCol - 1
    TrgColNm = ColNm(TrgColNum)
    
    For r = tblStRow To tblEdRow
        result = "no match"
        If lookupValRng.Value = Workbooks(WorkBookNm).Sheets(WorkSheetNm).Range(tblStColNm & r).Value Then
            Count = Count + 1
            If Count = occur Then
                result = Workbooks(WorkBookNm).Sheets(WorkSheetNm).Range(TrgColNm & r).Value
                Exit For
            End If
        End If
    Next r
    wlookup = result
End Function

Public Function ColNum(ByVal ColNm)
    ColNum = Range(ColNm & 1).Column
End Function

Public Function ColNm(ByVal ColNum)
    ColNm = Split(Cells(1, ColNum).Address, "$")(1)
End Function

Example of wlookup – Vlookup second or specific occurrence

Assume that you have the below data source.

vlookup_01

Formula Result Explanation
=wlookup(B2,Sheet1!$B$2:$C$7,2,1) John Vlookup the first occurance of Class 001
=wlookup(B2,Sheet1!$B$2:$C$7,2,2) Joe Vlookup the second occurance of Class 001
=wlookup(B2,Sheet1!$B$2:$C$7,2,3) June Vlookup the third occurance of Class 001
=wlookup(B2,Sheet1!$B$2:$C$7,2,4) no match Vlookup the forth occurance of Class 001, which is invalid

Outbound References

https://support.office.com/en-US/article/vlookup-function-adceda66-30de-4f26-923b-7257939faa65

Leave a Reply

Your email address will not be published.