Microsoft Office Tutorials and References
In Depth Information
USE VLOOKUP TO GET THE NTH MATCH
Breaking It Down: table_array is a range passed to the function. When the
function uses table_array, the future references to CELLS(nRow, 1) .Value
always look through the fi rst column of the lookup table. Later, VLOOKUPNTH is
assigned to be Cells(nRow , Col_index_number) . If Col_Index_Number
contains the number N, this refers to the nth column of the lookup table.
Additional Details: In my seminars, I frequently lament that VLOOKUP cannot
grab a value that appears to the left of the key fi eld. You could use a similar
approach to Barresse and Moran’s user-defi ned function to build a VLOOKUP
that will work to the left of the key fi eld:
Function VLOOKNEW(lookup_value, table_array As Range, _
col_index_num As Integer, CloseMatch As Boolean)
‘ Allows for col_index_num to be negative
‘ that matches the lookup value.
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
VLOOKNEW = "Not Found"
‘ if positive, treat as a regular VLOOKUP
If col_index_num > 0 Then
VLOOKNEW = Application.WorksheetFunction.VLookup(lookup_value, _
table_array, col_index_num, CloseMatch)
Else
‘ Do a VLOOKUP Left
nRow = Application.WorksheetFunction.Match(lookup_value, _
table_array.Resize(, 1), CloseMatch)
VLOOKNEW = table_array(nRow, 1).Offset(0, col_index_num)
End If
End Function
VLOOKNEW is similar to VLOOKUP , except that you can use a negative value for
the column index number (Figure 58).
 
Search JabSto ::




Custom Search