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.
Syntax:
=NVLOOKUP(lookup_value, lookup_range, column_number, nth, [optional closest-match])Arguments:
The function uses four required and one optional argument.
- 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 not available.
Explanation: How to get the nth value
If you are using 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.
