Microsoft Office Tutorials and References
In Depth Information
USE VLOOKUP TO GET THE NTH MATCH
Function VLOOKUPNTH(lookup_value, table_array As Range, _
col_index_num As Integer, nth_value)
‘ Allows for fi nding the nth item
‘ that matches the lookup 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
You need to add this function to your workbook’s VBA project. It works like
VLOOKUP, but instead of specifying FALSE as the fourth argument, you specify
which value match you want to return.
In Figure 57, a regular VLOOKUP appears in column F, and VLOOKUPNTH
appears in columns G:H.
Part
I
Figure 57. Formulas in G:H grab the nth match from the lookup table.
 
Search JabSto ::




Custom Search