Learn how to lookup and return the nth value (or the second, third or nth match) in Excel using the NVLOOKUP function.
No doubt, XLOOKUP is the #1 lookup function in Excel, but it has limitations. To get the nth match, you need to apply a workaround. For example, it is often required to return a specific value instance in a search. In this tutorial, we will introduce a user-defined function, NVLOOKUP. The demonstrated formula will replace the outdated INDEX and MATCH solutions.
Get nth match with NVLOOKUP
NVLOOKUP is like VLOOKUP, except you can return the nth match index value of the matching value in a range.
Steps to lookup the nth match in Excel:
- Open Excel
- Type =NVLOOKUP(F2,search_range,2,3)
- Press Enter
- The formula returns the 3rd match in the 2nd column.
Syntax:
=NVLOOKUP(lookup_value, lookup_range, column_number, nth, [optional closest-match])
Arguments:
- lookup_value: The first argument is the search value.
- lookup_range: The second argument is the range that contains the search_value
- column_number: The third argument is the column value to return.
- return_nth_instance: The fourth argument denotes which matched record to return.
- closest_match (optional): the argument is TRUE by default, which returns the closest match where an exact match does not exist. Use FALSE for exact match return. The result is an approximate match, not a search method change. It simply returns the last found match rather than an error where an exact match is unavailable.
Explanation: How to get the nth value
If you use NVLOOKUP, you do not need to create a helper column and construct a unique sequence to get the nth match from a range. First, select range B3:C14 and add a descriptive name to a range, for example, search_range. This is the range where we will find the lookup value.
In the example, we want to use “Product A” as a lookup value and find the nth match in the search_range. Then, set the column number argument to 2, and finally, enter the 3 as the nth match to find the third instance in a range.
Formula:
=NVLOOKUP(F2,search_range,2,3)
Download the practice file that contains the NVLOOKUP function.
To manually implement the function, you can use the code below:
Function NVLOOKUP(lookup_value As Variant, lookup_array As Variant, column_value As Integer, instance As Long, Optional closestMatch As Variant = True) As Variant
If column_value > lookup_array.Columns.count Then GoTo exiterr
If IsMissing(closestMatch) Then closestMatch = True
Dim i As Long, ii As Long: ii = 1
Dim rLen As Long: rLen = lookup_array.Rows.count
Dim fOne As Long, fint As Long
For i = 1 To rLen
If lookup_array(i, 1).Value = lookup_value Then fOne = i: fint = fint + 1
If fint = instance Then GoTo finish
Next
finish:
If closestMatch Then
NVLOOKUP = IIf(fOne, lookup_array(fOne, column_value), CVErr(xlErrNA))
Else
NVLOOKUP = IIf(fint = instance And fOne, lookup_array(fOne, column_value), CVErr(xlErrNA))
End If
Exit Function
exiterr:
NVLOOKUP = CVErr(xlErrNA)
End Function