Author: Oscar Cronquist Article last updated on February 17, 2018

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($B$3:$C$17, SMALL(IF((MMULT(ISNUMBER(SEARCH($F$2, $B$3:$C$17))*1, {1;1})>0)*(MMULT(ISNUMBER(SEARCH($F$3, $B$3:$C$17))*1, {1;1})>0), MATCH(ROW($B$3:$C$17), ROW($B$3:$C$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1: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)

Download Excel *.xlsx file

multiple-criteria-lookup-with-multiple-results-2v3.xlsx multiple-criteria-lookup-with-multiple-results-2v3.xlsx

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 row number 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.

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 […]

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? […]

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:

A record is returned if both search strings are found on the same row

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

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

Search each column for a string each and return multiple records – AND logic

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

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

Extract records where all criteria match if not empty

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

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 […]

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 […]

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 […]

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 […]