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

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Search/Lookup > Lookup with multiple criteria and display multiple search results using excel formula, part 3

← Previous post -

Filed in Excel, Search/Lookup on Aug.13, 2009. Email This article to a Friend

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:

Table of Contents

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

Lookup with multiple criteria and display multiple search results(vba)

Array formula in E9:

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER.

Copy cell E9 and paste it to cell range E9:G11.

Named ranges

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

Explaining array formula in cell E9

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1))

Step 1 - Find records where all three criteria match

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1))

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)

SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)

becomes

SEARCH("i", {"Federer"; "Djokovic"; "Murray"; "Davydenko"; "Roddick"; "Del Potro"; "Verdasco"; "Gonzalez"; "Wawrinka"; "Blake"; "Nalbandian"; "Robredo"; "Cilic"; "Stepanek"; "Almagro"})*SEARCH("e", {"Peter"; "Ted"; "Steven"; "Roy"; "James"; "William"; "John"; "Kevin"; "Brian"; "Jonny"; "Bill"; "George"; "Bruce"; "Michael"; "Philip"})*SEARCH("a", {" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})SEARCH("i", {"Federer"; "Djokovic"; "Murray"; "Davydenko"; "Roddick"; "Del Potro"; "Verdasco"; "Gonzalez"; "Wawrinka"; "Blake"; "Nalbandian"; "Robredo"; "Cilic"; "Stepanek"; "Almagro"})*SEARCH("e", {"Peter"; "Ted"; "Steven"; "Roy"; "James"; "William"; "John"; "Kevin"; "Brian"; "Jonny"; "Bill"; "George"; "Bruce"; "Michael"; "Philip"})*SEARCH("a", {" Roger "; " Novak "; " Andy "; " Nikolay "; " Andy "; " Juan Martin "; " Fernando "; " Fernando "; " Stanislas "; " James "; " David "; " Tommy "; " Marin "; " Radek "; " Nicolas "})

becomes

({#VALUE!; 7; #VALUE!; #VALUE!; 5; #VALUE!; #VALUE!; #VALUE!; 5; #VALUE!; 8; #VALUE!; 2; #VALUE!; #VALUE!})*({#VALUE!; 5; 2; 7; 2; 4; 6; 6; 4; 3; 3; #VALUE!; 3; 3; 7})*({#VALUE!; 5; 2; 7; 2; 4; 6; 6; 4; 3; 3; #VALUE!; 3; 3; 7})

and returns this array:

{#VALUE!; 175; #VALUE!; #VALUE!; 20; #VALUE!; #VALUE!; #VALUE!; 80; #VALUE!; 72; #VALUE!; 18; #VALUE!; #VALUE!}

Step 2 - Convert values to corresponding row numbers

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1))

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

IF(ISERROR(SEARCH($F$4, Last_name )*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1)

IF(ISERROR({#VALUE!; 175; #VALUE!; #VALUE!; 20; #VALUE!; #VALUE!; #VALUE!; 80; #VALUE!; 72; #VALUE!; 18; #VALUE!; #VALUE!}), "", ROW(Last_name)-MIN(ROW(Last_name))+1)

IF({TRUE;FALSE;TRUE; TRUE;FALSE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;FALSE;TRUE;TRUE}, "", ROW(Last_name)-MIN(ROW(Last_name))+1)

becomes

IF({TRUE;FALSE;TRUE; TRUE;FALSE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;FALSE;TRUE;TRUE}, "", ROW(A2:A16)-MIN(ROW(A2:A16))+1)

IF({TRUE;FALSE;TRUE; TRUE;FALSE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;FALSE;TRUE;TRUE}, "", {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-MIN({2;3;4;5;6;7;8;9;10;11;12;13;14;15;16})+1)

IF({TRUE;FALSE;TRUE; TRUE;FALSE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;FALSE;TRUE;TRUE}, "", {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-2+1)

becomes

IF({TRUE;FALSE;TRUE; TRUE;FALSE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;FALSE;TRUE;TRUE}, "", {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}-2+1)

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE}, "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15})

and returns

{""; 2; ""; ""; 5; ""; ""; ""; ""; ""; ""; ""; 13; ""; ""}

Step 3 - Return k-th smallest number

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1))

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1))

SMALL({""; 2; ""; ""; 5; ""; ""; ""; ""; ""; ""; ""; 13; ""; ""}, ROW(A1))

becomes

SMALL({""; 2; ""; ""; 5; ""; ""; ""; ""; ""; ""; ""; 13; ""; ""}, 1)

returns 2.

Step 4 - Return a value at the intersection of a particular row and column

=INDEX($A$2:$C$16, SMALL(IF(ISERROR(SEARCH($F$4, Last_name)*SEARCH($F$3, Middle_name)*SEARCH($F$2, First_name)), "", ROW(Last_name)-MIN(ROW(Last_name))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX($A$2:$C$16, 2, COLUMN(A1))

becomes

=INDEX($A$2:$C$16, 2, 1)

becomes

=INDEX({"Federer", "Peter", " Roger ";"Djokovic", "Ted", " Novak ";"Murray", "Steven", " Andy ";"Davydenko", "Roy", " Nikolay ";"Roddick", "James", " Andy ";"Del Potro", "William", " Juan Martin ";"Verdasco", "John", " Fernando ";"Gonzalez", "Kevin", " Fernando ";"Wawrinka", "Brian", " Stanislas ";"Blake", "Jonny", " James ";"Nalbandian", "Bill", " David ";"Robredo", "George", " Tommy ";"Cilic", "Bruce", " Marin ";"Stepanek", "Michael", " Radek ";"Almagro", "Philip", " Nicolas "}, 2, 1)

and returns "Djokovic"

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

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.

Lookup with multiple criteria and display multiple search results (VBA)

Where to copy vba code

Copy vba code below
Press Alt + F11
Insert a new module
Paste code into code window
Return to Excel

Array Formula in cell E9:

=Searchtbl(F2:F4;A2:C16)

How to create array formula

Select cell range E9:G11
Type above array formula
Press and hold Ctrl + Shift
Press Enter once
Release alla keys

Function Searchtbl(SrchRng As Variant, tbl As Variant) As Variant
'SrchRng must have equal number of cells as headers in table
Dim i, r, c As Single
Dim tempArray() As Variant
ReDim tempArray(tbl.Columns.Count - 1, 0)
tbl = tbl.Value
SrchRng = SrchRng.Value
For r = LBound(tbl, 1) To UBound(tbl, 1)
    i = 0
    For c = LBound(SrchRng) To UBound(SrchRng)
        If InStr(UCase(tbl(r, c)), UCase(SrchRng(c, 1))) = 0 Then
            i = 0
            Exit For
        Else
            i = i + 1
        End If
    Next c
    If i = UBound(tbl, 2) Then
        For c = LBound(tempArray, 1) To UBound(tempArray, 1)
            tempArray(c, UBound(tempArray, 2)) = tbl(r, c + 1)
        Next c
        ReDim Preserve tempArray(UBound(tempArray, 1), UBound(tempArray, 2) + 1)
        i = 0
    End If
Next r
ReDim Preserve tempArray(UBound(tempArray, 1), UBound(tempArray, 2) - 1)
Searchtbl = Application.Transpose(tempArray)
End Function

Download excel file *.xls

multiple-criteria-lookup-with-multiple-results-vba.xls

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

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 for multiple text strings in multiple cells in excel, part 2

Recommended categories

Comments (16)

16 Responses to “Lookup with multiple criteria and display multiple search results using excel formula, part 3”

Niranjan says:
August 22, 2009 at 9:37 pm
You are a genius. Formulas are simple, and easy to understand.
Reply
G says:
September 13, 2009 at 7:49 am
Could this be done to filter for 10 criteria?
Reply
admin says:
September 13, 2009 at 10:18 am
10 criteria in each column? 10 criteria in all columns?
Reply
Qasim says:
September 18, 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?
Reply
Qasim says:
September 18, 2009 at 12:10 pm
ignore my last comment... it works a treat!! (i put in one too many $ signs!!)
Thanks!
Reply
D says:
September 30, 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!
Reply
Oscar says:
October 1, 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/
Reply
Art says:
March 13, 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!!!
Reply
Min says:
March 16, 2011 at 7:42 pm
THis is great!!!
What if I want to search using 4 criteria? Can you please help me?
Thanks!
Reply
Oscar says:
March 17, 2011 at 10:40 am
Min,
I have changed the formula. I think the new formula is easier. I have also written an explanation, perhaps with help from explanation, you can now add a fourth criterion yourself? Otherwise, comment here again.
I uploaded a new file, as well.
Reply
RU says:
June 5, 2011 at 10:07 pm
Hi,
Your articles are really helpful! Can you please suggest if i want to find out the rows with fixed value in "First Name" but, if either of the criteria for "Middle Name" or "Last Name" will suffice. Also, i don't want repeated values in the final sheet.
For eg:
FN: a
MN: o
LN: o
Then, Davydenko Roy Nikolay should come only once.
Regards
Reply
Oscar says:
June 7, 2011 at 8:36 am
RU,
Read this post: Lookup with multiple criteria and display multiple unique search results (array formula)
Reply
Jim says:
September 22, 2011 at 11:53 pm
This solution was elegant and consistent! The only issue I am facing, (or so I've been told by an associate) is that as the number of entries increases, it would be best to write this functionality into a module(?) for the sake of performance. Any ideas, solutiobns or resources would be appreciated!! Thank you!!
Reply
Oscar says:
October 1, 2011 at 3:55 pm
Jim,
I have added a custom function to this post. I have not done any performance testing.
Reply
Stuart says:
September 20, 2012 at 11:35 am
Oscar = Legend / Demigod
Thanks for your time and skill explaining this for all us mortals.
Reply
- Oscar says:
  September 21, 2012 at 2:47 pm
  Stuart,
  Thank you for commenting!
  The explanations are not that hard to do. Select a cell containing a formula. Click in the formula bar. Press F9 and the formula is converted into a value. This also works for array formulas.
  Reply