# INDEX MATCH with multiple criteria

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions in cells B13:C13, the result is in cell D13.

The formula in cell D13 returns the first match where both cells meet the conditions on the same row.

### Table of Contents

## 1. INDEX MATCH 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 the above picture.

The formula uses two conditions, one is specified in cell B13 and the other one in C13. It looks for the first condition in cell range B3 to B10 and the second condition in C3 to C10 if both conditions are met on the same row the corresponding name from cell range D3:D10 is returned.

### 1.1 Explaining formula in cell D13

#### Step 1 - Concatenate lookup values

The ampersand character concatenates both values you want to look for.

B13&C13

becomes

"East"&"A"

and returns "EastA".

#### Step 2 - Concatenate lookup columns

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

B3:B10&C3:C10

returns {"WestC"; "EastE"; ... ; "WestH"}.

#### Step 3 - Make the array formula a regular formula

The INDEX function lets you convert some array formulas to regular formulas, this is one of them.

INDEX(B3:B10&C3:C10,)

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

MATCH("**EastA**", {"WestC"; "EastE"; "WestD"; "EastG"; "WestB"; "**EastA**"; "WestF"; "WestH"}, 0)

and returns 6. The value is in the 6th position in the array.

#### Step 4 - Return value from the same row

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

returns Jennifer in cell D13.

### 1.2 Array formula alternative

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.

## 2. INDEX MATCH - partial text multiple conditions Excel 365

The formula in cell D13 checks if a cell in B3:B10 contains text specified in cell B13 and on the same row, if the corresponding cell in C3:C10 contains the specified text in C13.

The formula returns a value from D3:D10 if both cells are on the same row and contain the given text strings.

Excel 365 formula in cell D13:

=FILTER(D3:D10,ISNUMBER(SEARCH($B$13,$B$3:$B$10)*SEARCH($C$13,$C$3:$C$10)))

This is a dynamic array formula that works only in Excel 365, it is entered as a regular formula, however, it spills values to cells below if needed.

For previous Excel versions, see this article: Lookup with multiple criteria and return multiple search results It uses INDEX MATCH to get values based on multiple partial conditions.

### 2.1 Explaining formula in cell D13

#### Step 1 - Search for partial text in cell range B3:B10

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SEARCH(*find_text*,*within_text*, [*start_num*])

This formula is copied to cells below in order to get all matching values. We need to use absolute references to lock cell ranges to prevent cell ranges from changing as we copy the cell and paste to cells below.

SEARCH($B$13,$B$3:$B$10)

returns {#VALUE!; #VALUE!; ... ; 4}.

Note that the SEARCH function returns a error value if the string is not found in a cell value.

#### Step 2 - Search for second partial text condition in cell range C3:C10

SEARCH($C$13, $C$3:$C$10)

returns {6; 4; ... ; #VALUE!}.

#### Step 3 - Multiply arrays

We will mutiply both arrays to perform AND logic by using the asterisk character.

SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10))

returns {#VALUE!; #VALUE!; ... ; #VALUE!}.

A number multipled with an error value returns a error value, however, a number multipled with a number returns a number.

#### Step 4 - Check if a value in the array is a number

The ISNUMBER function returns TRUE or FALSE based on the contents of the argument.

ISNUMBER(SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10))

returns {FALSE; FALSE;...; FALSE}.

#### Step 4 - Extract values based on logical array

The FILTER function returns values/rows based on a condition or criteria, it is only availabe to Excel 365 subscribers.

FILTER(D3:D10, ISNUMBER(SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10)))

returns {"Olivia"; "Jennifer"}.

## 3. INDEX MATCH - partial match multiple columns

The array formula in cell D13 extracts values from column D if the corresponding value in cell range B3:C3 contains the specified string in cell B13.

The condition in cell B13 is found in cells B4, B6, and C8. The corresponding values in column D are in D4, D6, and D8.

Array formula in cell D13:

### 3.1 Explaining formula in cell D13

#### Step 1 - Search cell range $B$3:$C$10 for string

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH($B$13, $B$3:$C$10)

returns {#VALUE!,#VALUE!;... ,#VALUE!}

#### Step 2 - Identify numbers in array

The ISNUMBER function returns TRUE or FALSE based on the contents of the argument.

ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1

The MMULT function can't calculate boolean values like TRUE and FALSE, we need to convert them to their numerical equivalents. TRUE - 1 , and FALSE - 0 (zero)

returns {0,0; 1,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0}.

#### Step 3 - Create an array containing 1

TRANSPOSE(COLUMN($B$3:$C$10)^0)

The COLUMN function returns row numbers based on a cell range.

COLUMN($B$3:$C$10)

returns {2,3}.

COLUMN($B$3:$C$10)^0

returns {1,1}.

TRANSPOSE(COLUMN($B$3:$C$10)^0)

becomes

TRANSPOSE({1, 1})

and returns {1; 1}.

#### Step 4 - Add values column-wise

The MMULT function calculatesÂ the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(*array1*,Â *array2*)

MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0))

becomes

MMULT({0,0; 1,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0}, {1; 1})

and returns {0; 1; 0; 1; 0; 1; 0; 0}.

#### Step 5 - Create an array from 1 to n

The ROW function lets you create numbers representing the rows based on a cell range.

The MATCH function finds the relative position of a given string in an array or cell range. This will create an array from 1 to n where n is the number of rows in cell range $D$3:$D$10.

MATCH(ROW($D$3:$D$10),ROW($D$3:$D$10))

and returns {1; 2; ... ; 8}. There are eight rows in $D$3:$D$10.

#### Step 6 - Replace 1 with corresponding row number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), "")

returns {""; 2; ""; 4; ""; 6; ""; ""}.

#### Step 7 - Extract k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*, *k*)

SMALL(IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), ""), ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; 4; ""; 6; ""; ""}, ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; 4; ""; 6; ""; ""}, 1)

and returns 2.

#### Step 8 - Get values based on row number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX($D$3:$D$10, SMALL(IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), ""), ROWS($A$1:A1)))

returns "Sophia".

The following formula is an Excel 365 dynamic array formula:

Absolute cell references are not required, the formula returns an array that spills values to cells below automatically.

## 4. Get Excel file

### Index match category

Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

### Excel categories

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

hi oscar, i have a question that expands this topic ;)

above, there are 3 columns: [ID][OrderDate][Product].

search result would be in the columne where you put the formula in. <-- i call this [Result]

questions:

1) What if there are multiple columns? [ID][OrderDate][ProductA][ProductB][ProductC]?

this would output to [ResultA][ResultB][ResultC]

2) there are 3 methods above. I prefer Method 1 (plain 'ol formula only).

If I have 5000 (and growing) list of ID/OrderDates, which method would be practical?

thanks!

david,

1) You say you prefer the first formula:=INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)) + ENTER

I modified the above formula to get the other two matching cells:

=INDEX($D$3:$F$6, SUMPRODUCT(--($C$10=ID_num), --($C$11=OrderDate), ROW(ProductA)-MIN(ROW(ProductA))+1), COLUMN(A1)) + ENTER.

Copy cell and paste it to the right as far as needed.

Remember, this formula can only match one row. If multiple rows match you need another formula.

2) I think you answer your own question. If the first formula works and is reasonable fast, IÂ´d also go with that one.

thx oscar, will try this out.

hi oscar,

i've tried the formula. it works but only if it's on the same sheet as the data.

if i move the formula on other sheet, it failed on the INDEX portion.

if u need a sample file, do let me know. i'll upload it somewhere for you :)

thanks!

assuming there is NO MATCH for ID and OrderDate ....by evaluating the formula when it's in INDEX portion, it gets

=INDEX($D$3:$F$6,0,1) <-- the row syntax=0 because there was no match.

however, it gets trickier.

if this formula is NOT within row 3-6, then it will generate an #VALUE error. (e.g. i put the formula at H8)

if i move this formula to a cell between row 3-6, the INDEX will pull the same value as per the row of the formula.

e.g. when the formula is at H4, the output is Green (D4). if formula at H5, the output is Yellow (D5).

so the question is, if there is NO MATCH for ID or OrderDate, how to output it as 0 (zero)?

thx :)

david,

Excel 2003:=IF(SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)=0, 0, INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1))) + Enter

Excel 2007:=IFERROR(INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)), 0) + Enter

hi oscar, thanks for your feedback.

i'm looking at Excel 2007 formula.

the IFERROR doesnt make any difference.

try this scenario:

1) move the B14 formula to H4

2) enter search ID = 4

3) u'll notice that it gives you the result as Green because the final formula step is =INDEX(Product,0,1) <-- the peculiarity occurs because the row syntax is 0.

Excel 2003's formula works though

david,

You are right, the excel 2007 formula is wrong.

Thanks!

Thank you, you have taken 2 days of frustration away.

Alfredo,

Thank you for commenting!

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