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:
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.
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