Author: Oscar Cronquist Article last updated on December 05, 2018

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:

Array formula in B20:

=INDEX($B$3:$H$12, SMALL(IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell B20

Step 1 - Compare criteria to data

The equal sign allows you to compare values, the resulting array contains TRUE or FALSE.

($B$3:$H$12=$B$16:$H$16)*1

becomes

({1, "Taylor", 39965, 0.333333333333333, "-", "Robinson", "Lee";2, "Anderson", 39965, 0.375, "-", "Clark", "Walker";3, "Thomas", 39965, 0.416666666666667, "-", "Rodriguez", "Hall";4, "Jackson", 39965, 0.458333333333333, "-", "Robinson", "Lee";5, "White", 39965, 0.541666666666667, "-", "Clark", "Walker";6, "Harris", 39966, 0.333333333333333, "-", "Rodriguez", "Hall";7, "Martinez", 39966, 0.375, "-", "Robinson", "Lee";8, "Thompson", 39966, 0.416666666666667, "-", "Clark", "Walker";9, "Garcia", 39966, 0.458333333333333, "-", "Rodriguez", "Hall";10, "Martinez", 39966, 0.541666666666667, "-", "Robinson", "Lee"}={0, 0, 39966, 0, 0, "Rodriguez", 0})*1

becomes

{FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE}*1

The MMULT function can't work with boolean values so in order to get that working we must multiply the array with 1.

{0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0}

Step 2 - Add values row-wise

MMULT(($B$3:$H$12=$B$16:$H$16)*1,{1;1;1;1;1;1;1})

becomes

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

and returns

{0;0;1;0;0;2;1;1;2;1}

Step 3 - Compare sum with the number of criteria

We know a record match if the number of criteria equals the sum returned from the MMULT function. The COUNTA function lets you count non empty cells in a given cell range.

MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16)

becomes

{0;0;1;0;0;2;1;1;2;1}=COUNTA($B$16:$H$16)

becomes

{0;0;1;0;0;2;1;1;2;1}=2

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

Step 4 - Replace TRUE with corresponding row number

The IF function allows you to return a value if the logical expression evaluates to TRUE and another if FALSE.

IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")

becomes

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

and returns

{"";"";"";"";"";6;"";"";9;""}

Step 5 - Extract the k-th smallest row number

The SMALL function lets you get the k-th smallest number in an array. SMALL( array, k)

SMALL(IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1))

becomes

SMALL({"";"";"";"";"";6;"";"";9;""}, ROWS($A$1:A1))

The ROWS function returns the number of rows in a cell reference, this particular cell reference is expanding when the cell is copied to cells below.

SMALL({"";"";"";"";"";6;"";"";9;""}, 1)

and returns 6.

Step 6 - Return value

The INDEX function returns a value from a cell range or array based on a row and column number.

INDEX($B$3:$H$12, SMALL(IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$H$12, 6, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$H$12, 6, 1)

and returns "6" in cell B20.

Get Excel *.xlsx file

Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2.xlsx

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Sum numbers between two dates

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Highlight duplicate records

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

How to compare two data sets

This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]