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.

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

Get the Excel file

INDEX-MATCH-multiple-conditionsv2.xlsx

Index match category

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

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

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

Excel categories

Comments (11)