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.

Read more related articles in the archive.

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

Where to copy vba code

  1. Copy vba code below
  2. Press Alt + F11
  3. Insert a new module
  4. Paste code into code window
  5. Return to Excel

Array Formula in cell E9:

=Searchtbl(F2:F4;A2:C16)

How to create array formula

  1. Select cell range E9:G11
  2. Type above array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. 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

Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Comments(17) Filed in category: Excel, Search and return multiple values

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

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

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Comments(3) Filed in category: Excel

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

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

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:

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

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Comments(27) Filed in category: Excel, Search and return multiple values

Search for a single text string in a single column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for multiple text strings in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in multiple columns and return corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values