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

Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]

INDEX MATCH – multiple results

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

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

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

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

### One Response to “INDEX MATCH with multiple criteria”

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

This problem is difficult to explain, but I will try. I’m creating a database to keep track of stock option spread trades that have numerous legs. Some of the trades are simple and only having two legs, with one position being long, and the other being short. But, some of the trades have 8-10 legs, and this creates a problem in calculating the value of each leg. For this example, lets open a credit spread trade and go long an option, and go short an option. This is trade number 500 and each leg is recorded on a separate row. I want to invest $1000 per leg, so we need to take the ABS difference of the two fill prices and divide it $1000. This will tell us the quantity purchased. Eventually, we’ll close the short leg, and open a new short leg. And each time, we need to calculate a quantity by performing the above calculation with the long leg. This cycle of closing the short leg and opening a new short leg can continue until the long leg is finally closed, which ends the trade. So, let’s say we have 8-legs in this trade, the first row contains the long leg, and the 7 rows below contain the short legs. Each row has an ID of 500 to identify all 8-rows as trade number 500. Each time a short leg is closed, and a new short leg is opened, we need to scan the table for ID 500, then scan those rows to find the long leg. We then need to go over 5 columns to locate the long leg fill price to use in the new quantity calculation. Hope all that makes sense! Thanks, Jeff