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

### Explaining formula in cell C14

The MATCH function returns the relative position of a value in an array or cell reference, this example has two values that makes it return an array of 2 values.

MATCH($C$12:$D$12, $B$3:$B$10, 0) returns {3, 2}

Green is found in B5 and that value is the third value in cell range B3:B10, the MATCH function returns 3.

Blue is found in B4 and that value is the second value in cell range B3:B10, the MATCH function returns 2.

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

The INDEX function makes this operation returning an array without you needing to enter the formula as an array formula.

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

The SMALL function returns the k-th smallest value, depending on what number ROWS($A$1:A1) returns.

ROWS($A$1:A1) has two cell references, the first one is an absolute reference and the second one is a relative cell reference.

The relative cell reference changes when you copy the cell and paste it to the cell below, ROWS($A$1:A2) returns 2. The SMALL function returns the second smallest number in C15.

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

Lastly the INDEX function retrieves a value in C3:C10 based on the number returned from the SMALL function.

### Download Excel *.xlsx file

INDEX and MATCH - multiple criteria and multiple results.xlsx

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

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

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