## 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 if possible, see above picture.

It combines both values you want to look for using the ampersand character.

B13&C13

Then it concatenates the two cell ranges also using the ampersand character, the INDEX function makes it a regular formula.

INDEX(B3:B10&C3:C10,)

The MATCH function then returns the relative position of the of the combined values, see picture above.

MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0) returns 6. The value is in the 6th position in the array.

INDEX(D3:D10,MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0))

becomes

INDEX(D3:D10, 6)

and returns Jennifer in cell D13.

If you don't mind array formulas, the only advantage is that it is somewhat smaller, use this formula:

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Download excel *.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 […]

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 […]

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

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

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