## INDEX and MATCH – multiple criteria and multiple results

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel 365 formula shown in section 2 is incredibly small, the new FILTER function is amazing.

#### Table of Contents

## 1. INDEX and MATCH - multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied to column Color.

This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria.

This should be an array formula, however, the second INDEX function makes this formula a regular formula.

### 1.1 Explaining formula in cell C14

#### Step 1 - Find relative position of specified conditions in C12:D12

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value*, *lookup_array*, *[match_type]*)

MATCH($C$12:$D$12, $B$3:$B$10, 0)

becomes

MATCH({"Green","Blue"},{"Brown"; "Blue"; "Green"; "Pink"; "Black"; "Orange"; "Yellow"; "Purple"},0)

and returns {3, 2}.

#### Step 2 - Get value based on the relative position

The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.

This is a workaround and it won't work in some array formula, it works fine in this one.

INDEX(*array*, *[row_num]*, *[column_num], [area_num]*)

INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), )

becomes

INDEX({3, 2}, )

and returns {3, 2}.

#### Step 3 - Calculate k-th smallest number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*, *k*)

SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))

becomes

SMALL({3, 2}, ROWS($A$1:A1))

The ROWS function counts the number of rows in a given cell reference, however, $A$1:A1 is a cell reference that grows automatically when you copy the cell and paste it to cells below. This makes the formula return a new value in each cell.

SMALL({3, 2}, ROWS($A$1:A1))

becomes

SMALL({3, 2}, 1)

and returns 2.

#### Step 4 - Get value

The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.

This is a workaround and it won't work in some array formula, it works fine in this one.

INDEX(*array*, *[row_num]*, *[column_num], [area_num]*)

INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1)))

becomes

INDEX($C$3:$C$10, 2)

and returns "C" in cell C14.

## 2. INDEX and MATCH - multiple criteria and multiple results (Excel 365)

The new FILTER function is amazing, it returns multiple values based on boolean value TRUE or FALSE or their numerical equivalents.

Dynamic array formula in cell G3:

Excel 365 returns arrays automatically and deploys values to adjacent cells as far as needed, Microsoft calls this behavior "spilling".

### Explaining formula in cell G3

#### Step 1 - Count values based on criteria

The COUNTIF function counts values based on a condition or criteria.

COUNTIF(*range*, *criteria*)

COUNTIF(E3:E4, B3:B10)

becomes

COUNTIF({"Green"; "Blue"}, {"Brown"; "Blue"; "Green"; "Pink"; "Black"; "Orange"; "Yellow"; "Purple"})

and returns {0; 1; 1; 0; 0; 0; 0; 0}.

#### Step 2 - Get values

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(C3:C10, COUNTIF(E3:E4, B3:B10))

becomes

FILTER(C3:C10, {0; 1; 1; 0; 0; 0; 0; 0})

becomes

FILTER({"H"; "C"; "F"; "B"; "G"; "D"; "A"; "E"}, {0; 1; 1; 0; 0; 0; 0; 0})

and returns {"C"; "F"}.

### Get Excel *.xlsx file

INDEX and MATCH - multiple criteria and multiple results.xlsx

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]

### 4 Responses to “INDEX and MATCH – multiple criteria and multiple results”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I'm building a workbook to search for any results that may use up to 34 criteria. So far I've built a formula from the website to fill six criteria, and I've hit a snag. The formula is creating duplicates. I want to avoid creating a list of results with duplicate values, then building a separate formula to create a list of unique values. Is there a way to do that all in one formula?

Here's the formula: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))

Name = B3:B59

Category = AH3:AM59

Justin,

Yes, it is possible. If you enter the formula in cell F2 the formula becomes:

=INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category)*(COUNTIF($F$1:F1,Category)=0), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))

Hey Good Day,

can u do it if you have multiple person in same organization like

Org | Name | Badge | GC

649238 Rayn 64982 08

649238 Jhon 78421 11

649238 sara 76899 06

when i setup it with index match it gives me Rayn duplicated.

Rayn

The following link takes you to an article that demonstrates how to extract records based on a lookup value:

https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple