## INDEX MATCH with multiple criteria

*Article updated on March 25, 2018*

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

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article