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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form