Author: Oscar Cronquist Article last updated on October 26, 2021

Partial match with two conditions and return multiple search results

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria.

The formula returns all rows that contain the criteria, the first condition is evaluated to column B and the second condition column C. Both cells must contain the strings respectively on the same row.

Question:

How do I search a list containing First name column and a last name column? I want to search both columns at the same time with two different criteria (one criteria for each column) and then display multiple search results.

multiple-criteria-lookup-with-multiple-results

1. Partial match with two conditions and return multiple search results

Partial match with two conditions and return multiple search results1

I created two search fields. First and last name in F2 and F3. The search results are presented in columns D and E. See the picture below.

The array formula in cell E8:

=INDEX($B$3:$C$17, SMALL(IF(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

Back to top

1.1 How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold CTRL + SHIFT keys.
  3. Press Enter key once.
  4. Release all keys.

Recommended articles

test
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

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

Back to top

1.2 Explaining the array formula in cell D6

Step 1 - Find the first partial match

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($F$2, $C$3:$C$17)

becomes

SEARCH("e",{" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

and returns

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}.

Step 2 - Second partial match

SEARCH($F$3, $B$3:$B$17)

becomes

SEARCH("o",{"Federer";"Djokovic";"Murray";"Davydenko";"Roddick";"Del Potro";"Verdasco";"Gonzalez";"Wawrinka";"Blake";"Nalbandian";"Robredo";"Cilic";"Stepanek";"Almagro"})

and returns

{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}.

Step 3 - Multiply arrays

The asterisk character lets you multiply the arrays creating AND logic meaning both values in the same position must be a number. This will only match rows where both conditons are met.

(SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))

becomes

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}*{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}.

Step 4 - Check if the value is a number

The array calculated in the previous step has error values that we must take care of. The ISNUMBER function returns TRUE if a value in the array is a number and FALSE for everything else including error values.

ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17)))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Step 5 - Replace boolean values with corresponding row numbers

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(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

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

ROW($B$3:$B$17) returns {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.

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 $B$3:$B$17.

MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17))

becomes

MATCH({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17})

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}.

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}, "")

and returns {""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}.

Step 6 - 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(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), ""), ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}, ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; 7; 8; ""; ""; ""; ""; ""; ""; ""}, 1)

and returns 7.

Step 7 - Get value from B3:C17 based on row and column number

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

INDEX($B$3:$C$17, SMALL(IF(ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17))), MATCH(ROW($B$3:$B$17), ROW($B$3:$B$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$17, 7, ROWS($A$1:A1)), COLUMNS($A$1:A1))

The COLUMNS function returns a new value in each cell, this makes sure that the same value is not returned twice.

INDEX($B$3:$C$17, 7, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$17, 7, 1)

returns ""Verdasco" in cell E8.

Back to top

2. Partial match with two conditions and return multiple search results - Excel 365

Partial match with two conditions and return multiple search results2

The image above shows a dynamic array formula that is much shorter than the formula in section 1 for previous Excel versions.

Excel 365 dynamic array formula in cell E8:

=FILTER($B$3:$C$17, ISNUMBER(SEARCH($F$2, $C$3:$C$17)*SEARCH($F$3, $B$3:$B$17)))

Back to top

Explaining formula in cell E8

Step 1 - Partial match first condition

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($F$2, $C$3:$C$17)

becomes

SEARCH("e",{" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

and returns

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}.

Step 2 - Partial match second condition

SEARCH($F$3, $B$3:$B$17)

becomes

SEARCH("o",{"Federer";"Djokovic";"Murray";"Davydenko";"Roddick";"Del Potro";"Verdasco";"Gonzalez";"Wawrinka";"Blake";"Nalbandian";"Robredo";"Cilic";"Stepanek";"Almagro"})

and returns

{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}.

Step 3 - AND logic

The asterisk character lets you multiply the arrays creating AND logic meaning both values in the same position must be a number. This will only match rows where both conditons are met.

SEARCH($F$2, $C$3:$C$17)*SEARCH($F$3, $B$3:$B$17)

becomes

{5; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 3; 3; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!}*{#VALUE!; 3; #VALUE!; 9; 2; 6; 8; 2; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!; 7}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}.

Step 4 - Check if number

The array calculated in the previous step has error values that we must take care of. The ISNUMBER function returns TRUE if a value in the array is a number and FALSE for everything else including error values.

ISNUMBER((SEARCH($F$2, $C$3:$C$17))*(SEARCH($F$3, $B$3:$B$17)))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 24; 6; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Step 5 - Extract records

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

FILTER(array, include, [if_empty])

FILTER($B$3:$C$17, ISNUMBER(SEARCH($F$2, $C$3:$C$17)*SEARCH($F$3, $B$3:$B$17)))

becomes

FILTER($B$3:$C$17, {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns

{"Verdasco", " Fernando "; "Gonzalez", " Fernando "}.

Back to top

3. Get Excel file

Get the Excel file


multiple-criteria-lookup-with-multiple-results1.xls

Back to top