Lookup nth match in table using INDEX & MATCH function

In this article, we will learn how to Lookup nth match in table using INDEX & MATCH function.

Scenario:

For instance, We need to find the numerically nth match and its corresponding results from the table. We need some general formula which helps in finding the nth match as to get the required value.

How to Solve the Problem?

For the formula to understand first we need to revise a little about the following functions

1. INDEX function
2. MATCH function

Now we will make a formula using the above functions. MATCH function will return the index of the lowest match from the range. The INDEX function takes the row index as argument and returns its corresponding required results. This function finds the 

Generic Formula:

data : array of values in the table without headers

range :  lookup_array for the lowest match

n : number, nth match

match_type : 1 ( exact or next smallest ) or 0 ( exact match) or -1 ( exact or next largest )

col_num : column number, required value to retrieve from the table column.

Example:

The above statements can be confusing to understand. So let’s understand this by using the formula in an example

Here we have a table having details about the world continent. We need to find the country code from the given table from the lookup value as country name.

Named range used for table (E5:I10) & range (I5:I10) array.
Here we used the named range instead for cell reference array as it's easy to understand. We need to look for all the details where Price value is minimum or lowest in the range.
Use the above formula to get the first detail which is capital
Use the formula in the G6 cell:

Named ranges

table (E5:I10)

range (I5:I10)

Explanation:

• SMALL function finds the first lowest match in range and returns the value to the MATCH function.
• MATCH function matches the exact minimum value match in the Price range and returns its row index to the INDEX function.
• The INDEX function finds the Capital value having ROW index and 4th column in the table named range.

The formula returns all results for the first lowest value. Now copy the formula to the get the second smallest or third lowest just have to change the n argument under SMALL function to get the different results.

Now just change the n value and get the results as shown below.

As you can see from the above snapshot we obtained all the details matching nth value in the table. Extract details from the table using the above stated formula.

Example:

You can also use LARGE function to get the nth match from top or nth highest match from the range and returns its corresponding results.

Generic Formula:

data : array of values in the table without headers

range :  lookup_array for the highest match

n : number, nth match

match_type : 1 ( exact or next smallest ) or 0 ( exact match) or -1 ( exact or next largest )

col_num : column number, required value to retrieve from the table column.

Example:

The above statements can be confusing to understand. So let’s understand this by using the formula in an example

Here we have a table having details about the world continent. We need to find the country code from the given table from the lookup value as country name.

Named range used for table (E5:I10) & range (I5:I10) array.
Here we used the named range instead for cell reference array as it's easy to understand. We need to look for all the details where Price value is maximum or highest in the range.

Use the above formula to get the first detail which is capital
Use the formula in the G6 cell:

Named ranges

table (E5:I10)

range (I5:I10)

Explanation:

• LARGE function finds the first highest match in range and returns the value to the MATCH function.
• MATCH function matches the exact nth match value match in the Price range and returns its row index to the INDEX function.
• The INDEX function finds the Capital value having ROW index and 4th column in the table named range.

The formula returns all results for the first lowest value. Now copy the formula to the get the second smallest or third lowest just have to change the n argument under SMALL function to get the different results.

Now just change the n value and get the results as shown below.

As you can see from the above snapshot we obtained all the details matching nth value in the table. Extract details from the table using the above stated formula.

Here are some observational noter while using the above formula.

Notes: 

1. Value of n cannot be smaller than 1 or greater than length of range
2. The function returns the #NA error if the lookup array argument to the MATCH function is not of same length of the table array.
3. The formula returns an error if lookup_value doesn't match the value in the table lookup_array.
4. The function matches exact value as the match type argument to the MATCH function is 0.
5. The lookup values can be given as cell reference or directly using quote symbol ( " ) in the formula as arguments.

Hope you understood how to Lookup nth match in table using INDEX & MATCH function. Explore more articles on Excel lookup value here. Please feel free to state your queries below in the comment box. We will certainly help you.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Related Articles

Use INDEX and MATCH to Lookup Value : INDEX & MATCH function to look up value as required.

SUM range with INDEX in Excel : Use INDEX function to find the SUM of the values as required.

How to use the INDEX function in Excel : Find the INDEX of array using the INDEX function explained with example.

How to use the MATCH function in Excel : Find the MATCH in the array using the INDEX value inside MATCH function explained with example.

How to use LOOKUP function in Excel : Find the lookup value in the array using the LOOKUP function explained with example.

How to use the VLOOKUP function in Excel : Find the lookup value in the array using the VLOOKUP function explained with example.

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel