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

Answer:

I created two search fields. First and last name in E2 and G2. The search results are presented in column D and E. See picture below.

multiple-criteria-lookup-with-multiple-results1

The array formula in cell D6:

=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16))*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

Recommended article

Lookup with multiple criteria and display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Comments(5) Filed in category: Excel, MMULT function, Search and return multiple values, Unique distinct values

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.
  3. Press Enter once.
  4. Release all keys.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Copy cell D6 and paste it down as far as needed.

The array formula in cell E6:

=INDEX($A$2:$A$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16))*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell E6 and paste it down as far as needed.

Recommended reading

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]

Comments(28) Filed in category: Excel, Filter records

Explaining the array formula in cell D6

Step 1 - Find rows where all criteria match

=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

Search() function 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, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16) returns this array:

(#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6,  #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE)

(SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16)) contains only absolute references.

SEARCH and FIND functions

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

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

Step 2 - Identify numbers in array

=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

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

ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16)))

becomes

ISNUMBER(#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6,  #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE))

and returns this array:

(FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)

Step 3 - Convert boolean values into row numbers

=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

IF() checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, "")

becomes

IF( (FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, "")

becomes

IF( (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) , "")

and returns this array: ("","","","","", 6, 7, "", "", "", "", "", "", "")

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 4 - Return k-th samllest value

=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

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

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

becomes

SMALL(("","","","","", 6, 7, "", "", "", "", "", "", ""), ROW(A1))

becomes

SMALL(("","","","","", 6, 7, "", "", "", "", "", "", ""), 1)

and returns 6

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

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($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16))*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))

becomes

=INDEX($B$2:$B$16,6) and returns Fernando in cell D6.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Download excel sample file for this tutorial.

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

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