## INDEX MATCH with multiple criteria

*Article last 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

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