How to find nth Occurrence with Multiple Criteria Using INDEX/MATCH

This post explains that how to get the nth occurrence value with multiple criteria using INDEX and MATCH in excel. How to find the first, second or nth match with multiple criteria in excel. How to return the nth match on a multiple criteria using INDEX and MATCH.

In the previous post, we talked that get the nth match value with only one criteria.

Also, we also talked that how to Lookup the Value with Multiple Criteria to find the first occurrence match in excel.

If you want to find the nth occurrence with multiple criteria, you can use a combination with the INDEX function, SMALL function, nested IF function and ROW function to create a complex excel formula like this:

=INDEX(Array,SMALL(IF(Range1=Criteria1,IF(Range2=Criteria2,IF(Range3=Criteria3,IF(Range4=Criteria4,IF(Range5=Criteria5,ROW(Array)-ROW(INDEX(Array,1))+1))))),nth))

For example, if you want to get the bonus value in the excel range D2:D10 that must meet two criteria, and the first one is that the member name is equal to the string “jenny” in the range A2:A10, and the second criteria is that the location is equal to “London” in the range B2:B10. So we can write down the following array formula:

=INDEX(D2:D10, SMALL(IF(A2:A10="jenny",IF(B2:B10="London", ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2))

Note: after entering this formula in the formula bar, you need to press CTRL+SHIFT+Enter key to convert it as array formula.

Let’s see how this formula works:

=ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1

find-nth-match-multiple-criteria1

This formula returns the relative position of the range A2:A10, it returns an array result like this:

{1;2;3;4;5;6;7;8;9}

For More detailed description, you can continue to read this post: How to Find the Relative Position in a Range or Table

 

=IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1))

find-nth-match-multiple-criteria2

This function is a nested IF function, it will check each item of range A2:A10 firstly and if it is equal to the string “jenny”, if TRUE, then continue to check each item of range B2:B10 if it is equal to the string “London”, if TRUE, return the relative position of row. Otherwise, return FALSE. So this formula returns an array result like this:

{FALSE;2;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE}

 

=SMALL(IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2)

find-nth-match-multiple-criteria3

The SMALL function returns the second smallest value from the array result returned by the above nested IF function. It returns 5.

So far, we got the position of the second occurrence of bonus value that meet two criteria as requested above.

 

=INDEX(D2:D10, SMALL(IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2))

find-nth-match-multiple-criteria4

The INDEX function extract the values at a position that returned by the SMALL function. So it is the bonus value of the second occurrence in the range D2:D10. It is 165.


Related Formulas

  • Get nth Match with One Criteria using INDEX/MATCH
    if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula:=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))…
  • Reverse a List or Range
    If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula. you can use the following formula:=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)…
  • Transpose Values Based on the Multiple Lookup Criteria
    If you want to lookup the value with multiple criteria, and then transpose the last results, you can use the INDEX function with the MATCH function to create a new formula.…
  • Lookup the Value with Multiple Criteria
    If you want to lookup the value with multiple criteria in a range, you can use a combination with the INDEX function and MATCH function to create an array formula.…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel nested IF function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions.The syntax of Nested IF function is as below:=IF(Condition_1,Value_if_True_1,IF(Condition_2,Value_if_True_2,Value_if_False_2))….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

 

Related Posts
How to Sum the Smallest N Values in Excel
How to Sum the Smallest N Values in Excel 15

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel
How to Create Dynamical Drop-Down List 14

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ ...

How to Find the Smallest Value and Smallest Positive Value in Excel
Find the Smallest Positive Value 9

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate ...

How to Convert Multiple Rows into a Single Row in Excel
convert multiple rows into a single row6

This post will guide you how to convert multiple rows or columns into a single row with a formula in Excel 3013/2016. How do I convert groups of rows to a single row of columns with VBA Macro in Excel. ...

How to Remove Numbers from Text in Excel
remove numbers from text2

This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers ...

How to Highlight Every Other Row Using Conditional Formatting in Excel
highlight every other row6

This post will guide you how to highlight every other row in Excel. How do I shade every other row with conditional formatting in Excel. Highlight Every Other Row Video: Highlight Every Other Row Highlight Every Other Row Assuming that ...

How to Get the First, 2nd or Nth Match Using Vlookup/Index/Match
Find the First Match Value Using VLOOKUP4

This post will guide you how get the nth matching values with VLOOKUP function in Excel. How do I find the nth match value with Index/Match formula in Excel. How to find the first, second, third or nth matching value ...

Ignoring Blank or Zero Cells with Conditional formatting
ignore blank zero cells in 7

This post will guide you how to make conditional formatting ignore blank cells or zero cell in Excel. How do I force blank cells or zero cells to be ignored in conditional formatting in Excel. How to ignore blank cells ...

Find the Earliest and Latest Date in a Range of Dates in Excel
find earliest date4

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest ...

Find Closest Value or Nearest Value in a Range in Excel
find closest value3

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value ...

Sidebar