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)

Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Comments(17) Filed in category: Excel, Search and return multiple values

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Comments(3) Filed in category: Excel

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Comments(27) Filed in category: Excel, Search and return multiple values

Search for a single text string in a single column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for multiple text strings in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in multiple columns and return corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values