Advanced Custom VLOOKUP Formula Help

You may encounter a problem with the VLOOKUP function the VLOOKUP formula will only return the first solution it finds. How can the VLOOKUP function return all correct entries? The easiest method to accomplish this will require VBA. We must create a custom function in Excel. Begin by pressing ALT+F11 to open VBA window. Next, click Insert- Module. Try the following code:


Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, _Optional Nth As Long = 1)
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function. 'Data does NOT need to be sorted, and it searches for EXACT match.
'if ColRef is omitted, uses the number of columns in myrange
'if Nth is omitted, returns the first value found

Dim Count, i As Long
Dim MySheet As Worksheet

Count = 0
Set MySheet = Sheets(MyRange.Parent.Name)
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
Count = Count + 1
If Count = Nth Then
VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
Exit Function
End If
End If
Next i
VlookupNth = "Not Found"
End Function

Now, once the VBA is complete, close the window and save your file. Now you can enter this custom formula in the worksheet to pick any row number that you want:

=VLOOKUPNTH(LOOKUP_VALUE, LOOKUP_ARRAY, COLUMN_NUMBER, Nth_Number_Row)

So, if you want to choose the value in the 4th column of the second row where the value matches exactly, then the Excel formula would be something like this:

=VLOOKUPNTH(Lookup_Value,LOOKUP_ARRAY,4,2)

What's the difference between this advanced VLOOKUP formula and the standard VLOOKUP? If you have a normal VLOOKUP formula like this =VLOOKUP(A1,B:C,2,FALSE) whereas the VLOOKUPNTH formula would look like this =VLOOKUPNTH(A1,B:C,2,2). The last digit corresponds to the nth part which will return the nth occurrence of the lookup value.

Now you're probably wondering what happens if there are more than two occurrences? What if there are 200 occurrences of the data? Well, then you would need 200 formulas with one returning the first value, one returning the second and so on. You don't want to have to manually edit all 200 formulas so insert the ROW function in order to increment the nth as you fill the formula down. =VLOOKUPNTH($A$1,B:C,2,ROW(A1))


Check out our follow-up post: advanced custom HLOOKUP formula.