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

Read more related articles in the archive.

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle and last name column could I only display the values in which all three cases are true?

This blog article answers a question in this article: Lookup with multiple criteria and display multiple search results using excel formula

Answer:

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

Array formula in E9:

=IF(SUM(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), 1, 0))<ROWS($E8:$E$8), "", INDEX(First_name, SMALL(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), ROW(INDIRECT("1:"&ROWS(Last_name))), ""), ROWS($E8:$E$8)))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Array formula in F9:

=IF(SUM(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), 1, 0))<ROWS($E8:$E$8), "", INDEX(Middle_name, SMALL(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), ROW(INDIRECT("1:"&ROWS(Last_name))), ""), ROWS($E8:$E$8)))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Array formula in G9:

=IF(SUM(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), 1, 0))<ROWS($E8:$E$8), "", INDEX(Last_name, SMALL(IF(ISNUMBER(SEARCH($F$2, First_name)*(SEARCH($F$4, Last_name))*(SEARCH($F$3, Middle_name))), ROW(INDIRECT("1:"&ROWS(Last_name))), ""), ROWS($E8:$E$8)))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
First_name (C2:C16)
Middle_name (B2:B16)
Last_name (A2:A16)
What is named ranges?

Download excel example file.
multiple-criteria-lookup-with-multiple-results.xls
(Excel 97-2003 Workbook *.xls)

Functions 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)

=IF(SUM(IF(ISNUMBER(SEARCH($F$2;First_name)*(SEARCH($F$4;Last_name))*(SEARCH($F$3;Middle_name)));1;0))<ROWS($E8:$E$8);"";INDEX(First_name;SMALL(IF(ISNUMBER(SEARCH($F$2;First_name)*(SEARCH($F$4;Last_name))*(SEARCH($F$3;Middle_name)));ROW(INDIRECT("1:"&ROWS(Last_name)));"");ROWS($E8:$E$8))))

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. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  3. Lookup with multiple criteria and display multiple search results using excel formula
  4. Search and display all cells that contain all search strings in excel
  5. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  6. Search for a text string in an excel table
  7. Search for a cell value in an excel table
  8. Search and display a range of values in excel
  9. Search for multiple text strings in multiple cells and use in data validation in excel