Question:

I want to search two columns with two search strings? The strings can be anywhere in these two columns but they both have to be somewhere on the same row to match. The search is not case sensitive.

Answer:

Array formula in cell D7:

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW(A2:A16)-MIN(ROW(A2:A16))+1), ROW(A1)), COLUMN(A1))

How to create an array formula

  1. Select cell D7
  2. Click in formula bar
  3. Copy and paste array formula to formula bar
  4. Press and hold Ctrl + Shift
  5. Press Enter
  6. Release all keys

How to copy an array formula

  1. Select cell D7
  2. Copy the cell (Ctrl + c)
  3. Select cell range D7:D12
  4. Paste (Ctrl + v)
  5. Copy cell range D7:D12 (Ctrl + c)
  6. Select cell range E7:E12
  7. Paste (Ctrl + v)

Explaining array formula in cell D7

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

Step 1 - Find cells containing first string

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)

becomes

SEARCH("i", {"Federer"; "Djokovic"; "Murray"; "Davydenko"; "Roddick"; "Del Potro"; "Verdasco"; "Gonzalez"; "Wawrinka"; "Blake"; "Nalbandian"; "Robredo"; "Cilic"; "Stepanek"; "Almagro"}&{" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

becomes

SEARCH("i", {"Federer Roger ";"Djokovic Novak ";"Murray Andy ";"Davydenko Nikolay ";"Roddick Andy ";"Del Potro Juan Martin ";"Verdasco Fernando ";"Gonzalez Fernando ";"Wawrinka Stanislas ";"Blake James ";"Nalbandian David ";"Robredo Tommy ";"Cilic Marin ";"Stepanek Radek ";"Almagro Nicolas "})

and returns this array:

{#VALUE!;7;#VALUE!;12;5;20;#VALUE!;#VALUE!;5;#VALUE!;8;#VALUE!;2;#VALUE!;10}

Step 2 - Find cells containing second string

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)

becomes

SEARCH("r", {"Federer"; "Djokovic"; "Murray"; "Davydenko"; "Roddick"; "Del Potro"; "Verdasco"; "Gonzalez"; "Wawrinka"; "Blake"; "Nalbandian"; "Robredo"; "Cilic"; "Stepanek"; "Almagro"}&{" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

becomes

SEARCH("r", {"Federer Roger ";"Djokovic Novak ";"Murray Andy ";"Davydenko Nikolay ";"Roddick Andy ";"Del Potro Juan Martin ";"Verdasco Fernando ";"Gonzalez Fernando ";"Wawrinka Stanislas ";"Blake James ";"Nalbandian David ";"Robredo Tommy ";"Cilic Marin ";"Stepanek Radek ";"Almagro Nicolas "})

and returns this array:

{5;#VALUE!;3;#VALUE!;1;8;3;12;4;#VALUE!;#VALUE!;1;9;10;6}

Step 3 - Convert numbers into row numbers in array

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

IF(logical_test;[value_if:true];[value_if_false]) checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1)

becomes

IF(ISERROR({#VALUE!;7;#VALUE!;12;5;20;#VALUE!;#VALUE!;5; #VALUE!;8;#VALUE!;2;#VALUE!;10}*{5;#VALUE!;3;#VALUE!;1;8;3; 12;4;#VALUE!;#VALUE!;1;9;10;6}, "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1)

becomes

IF(ISERROR({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 5;160;#VALUE!;#VALUE!;20;#VALUE!;#VALUE!; #VALUE!;18;#VALUE!;60}, "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1)

becomes

IF(ISERROR({#VALUE!;#VALUE!;#VALUE!; #VALUE!;5;160;#VALUE!;#VALUE!;20;#VALUE!;#VALUE!; #VALUE!;18;#VALUE!;60}, "", {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-MIN({2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})+1)

becomes

IF(ISERROR({#VALUE!;#VALUE!;#VALUE!; #VALUE!;5;160;#VALUE!;#VALUE!;20;#VALUE!; #VALUE!;#VALUE!;18;#VALUE!;60}, "", {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-2+1)

becomes

IF(ISERROR({#VALUE!;#VALUE!;#VALUE!; #VALUE!;5;160;#VALUE!;#VALUE!;20;#VALUE!; #VALUE!;#VALUE!;18;#VALUE!;60}, "", {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})

and returns

{"";"";"";"";5;6;"";"";9;"";"";"";13;"";15}

Step 4 - Return k-th smallest number in array

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1))

becomes

SMALL({"";"";"";"";5;6;"";"";9;"";"";"";13;"";15}, ROW(A1))

becoes

SMALL({"";"";"";"";5;6;"";"";9;"";"";"";13;"";15}, 1)

and returns 5.

Step 5 - Return a value of the cell at the intersection of a particular row and column

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range

=INDEX($A$2:$B$16, SMALL(IF(ISERROR(SEARCH($E$2, $A$2:$A$16&$B$2:$B$16)*SEARCH($E$3, $A$2:$A$16&$B$2:$B$16)), "", ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX($A$2:$B$16, 5, COLUMN(A1))

becomes

=INDEX($A$2:$B$16, 5, 1)

becomes

=INDEX({"Federer", " Roger ";"Djokovic", " Novak ";"Murray", " Andy ";"Davydenko", " Nikolay ";"Roddick", " Andy ";"Del Potro", " Juan Martin ";"Verdasco", " Fernando ";"Gonzalez", " Fernando ";"Wawrinka", " Stanislas ";"Blake", " James ";"Nalbandian", " David ";"Robredo", " Tommy ";"Cilic", " Marin ";"Stepanek", " Radek ";"Almagro", " Nicolas "}, 5, 1)

and returns Roddick in cell D7.

Download excel sample file for this tutorial.

multiple-criteria-lookup-with-multiple-results-2.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this article

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) returns the rownumber of a reference

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

SMALL(array,k) returns the k-th smallest row number in this data set.

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

This blog article is one out of five articles on the same subject.

Read more related articles in the archive.