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))) + CTRL + SHIFT + ENTER. 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.

Explaining the array formula in cell D6

Step 1

=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).

In row 8 and 9 both the characters "o" and "e" are found in each column A and column C and returns a number in column F.

(SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16)) creates 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.

Step 2

=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, SMALL(IF(ISNUMBER(#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6,  #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE)), 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((#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6,  #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE) reurns this array:

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

Step 3

=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, SMALL(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, ""), ROW(A1)))

becomes

=INDEX($B$2:$B$16, SMALL(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) , ""), ROW(A1)))

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

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) , "") returns this array: ("","","","","", 6, 7, "", "", "", "", "", "", "")

Step 4

=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(("","","","","", 6, 7, "", "", "", "", "", "", ""), ROW(A1)) returns 6 and 7

Step 5

Array formula in 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)))

becomes

=INDEX($B$2:$B$16,6) is Fernando

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

Array formula in D7:

=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(A2)))

becomes

=INDEX($B$2:$B$16,7) is Fernando

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

Download excel sample file for this tutorial.

multiple-criteria-lookup-with-multiple-results.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.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  2. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  3. Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
  4. How to return multiple values using vlookup in excel
  5. Search for multiple text strings in multiple cells and use in data validation in excel
  6. Lookup values in a range using two or more criteria and return multiple matches in excel