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.

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.

wookup ( 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(ColNm)
    ColNum = Range(ColNm & 1).Column
End Function

Public Function ColNm(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.

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

Excel Vlookup second matched value or specific occurrence