# 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 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)) 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) 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)) 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

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array

What is Excel Array? In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values. The dimension ...

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract all Partial Matches

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate ...