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.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  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