Question: I second G's question: can this be done for more than 3?

i.e.
(Instead of last name, middle, first)
customer#, cust name, appt date, appt time, venue, coordinator, assistant

A question asked in this post:
Lookup with multiple criteria and display multiple search results using excel formula, part 3

Answer:

Search a list with multiple criteria and return results in excel

Array formula in B25:

=SMALL(IF(ISNUMBER(SEARCH(Search_customer, Customer)*SEARCH(Search_cust_name, Cust_name)*SEARCH(Search_Appt_date, Appt_date)*SEARCH(Search_Appt_time, Appt_time)*SEARCH(Search_venue, Venue)*SEARCH(Search_Coordinator, Coordinator)*SEARCH(Search_Assistant, Assistant)), ROW(Customer)-MIN(ROW(Customer))+1), ROWS(B24:$B$24)) + CTRL + SHIFT + ENTER copied down as far as needed.

Formula in C25:

=INDEX(Customer, $B25) + ENTER

Formula in D25:

=INDEX(Cust_name, $B25) + ENTER

Formula in E25:

=INDEX(Appt_date, $B25) + ENTER

Formula in F25:

=INDEX(Appt_time, $B25) + ENTER

Formula in G25:

=INDEX(Venue, $B25) + ENTER

Formula in H25:

=INDEX(Coordinator, $B25) + ENTER

Formula in I25:

=INDEX(Assistant, $B25) + ENTER

Named ranges

Customer (B3:B12)
Cust_name (C3:C12)
Appt_date (D3:D12)
Appt_time (E3:E12)
Venue (F3:F12)
Coordinator (G3:G12)
Assistant (H3:H12)

Search_customer (D15)
Search_cust_name (D16)
Search_Appt_date (D17)
Search_Appt_time (D18)
Search_venue (D19)
Search_Assistant (D20)
Search_Coordinator (D21)
What is named ranges?

Download excel example file.
Lookup with multiple criteria and display multiple search 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.

Related posts:

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

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

Lookup with multiple criteria and display multiple search results using excel formula

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

Search and display all cells that contain all search strings in excel