Lookup with multiple criteria and display multiple search results using excel formula, part 3
This blog article is one out of five articles on the same subject.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- 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
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:
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)
This blog article is one out of five articles on the same subject.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- 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
Read more related articles in the archive.
Related posts:
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- 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
- Search and display all cells that contain all search strings in excel
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Search for a text string in an excel table
- Search for a cell value in an excel table
- Search and display a range of values in excel
- Search for multiple text strings in multiple cells and use in data validation in excel



August 22nd, 2009 at 9:37 pm
You are a genius. Formulas are simple, and easy to understand.
September 13th, 2009 at 7:49 am
Could this be done to filter for 10 criteria?
September 13th, 2009 at 10:18 am
10 criteria in each column? 10 criteria in all columns?
September 18th, 2009 at 11:49 am
Hi,
i had a similar query and i believe your example is the way forward for me but i can't get my search to work.
like your example i'm searching 3 columns but in a table of 8 colums and the search function is on a seperate tab. there is also a gap between the columns i'm searching. i also decided not to name the ranges as they may change. are any of the above affecting the formala or is it just me?
September 18th, 2009 at 12:10 pm
ignore my last comment... it works a treat!! (i put in one too many $ signs!!)
Thanks!
September 30th, 2009 at 3:05 am
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
Thanks for putting this up!
October 1st, 2009 at 1:24 pm
D,
See this blog post: http://www.get-digital-help.com/2009/10/01/lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4/
March 13th, 2010 at 5:45 am
I applied this in my spreadsheet and worked perfectly well.
However, I noticed that if a cell in the search range is blank, it does not include it in the results. In your example, if B2 was blank instead of having the value "Ted." that name would not show up in the search results (E9:E11).
Is there a work around for this? The spreadsheet I have may have blank cells but I need them to show in the results page.
Thanks!!!