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

RU asks:

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.

Answer:

Array formula in cell F8:

=INDEX($B$3:$D$17, SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))*1,{1;1;1})>0, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

How to copy array formula

  1. Copy (Ctrl + c) cell E9
  2. Paste (Ctrl + v) array formula on cell range E9:G11

Explaining formula in cell F8

Step 1 - Search for criteria

The SEARCH function allows you to find a string in a cell and it's position. It also allows you to search for multiple strings in multiple cells if you arrange values in a way that works. That is why I use the TRANSPOSE function to transpose the values.

SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17)

becomes

SEARCH(TRANSPOSE({"a"; "o"; "o"}), $B$3:$D$17)

becomes

SEARCH(TRANSPOSE({"a"; "o"; "o"}), {"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", "Ted", " James ";"Nalbandian", "Bill", " David ";"Robredo", "George", " Tommy ";"Cilic", "Bruce", " Marin ";"Stepanek", "Michael", " Radek ";"Almagro", "Pihilip", " Nicolas "})

and returns

{#VALUE!, #VALUE!, 3;#VALUE!, #VALUE!, 3;5, #VALUE!, #VALUE!;2, 2, 5;#VALUE!, #VALUE!, #VALUE!;#VALUE!, #VALUE!, #VALUE!;5, 2, 9;5, #VALUE!, 9;2, #VALUE!, #VALUE!;3, #VALUE!, #VALUE!;2, #VALUE!, #VALUE!;#VALUE!, 3, 3;#VALUE!, #VALUE!, #VALUE!;5, #VALUE!, #VALUE!;1, #VALUE!, 5}.

Step 2 - Convert numbers to true

The ISNUMBER function returns TRUE if value is a number and FALSE for everything else even errors which is very handy in this case, the search function returns #VALUE! error if a string is not found in a particular cell.

--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17)))

becomes

--(ISNUMBER({#VALUE!, #VALUE!, 3;#VALUE!, #VALUE!, 3;5, #VALUE!, #VALUE!;2, 2, 5;#VALUE!, #VALUE!, #VALUE!;#VALUE!, #VALUE!, #VALUE!;5, 2, 9;5, #VALUE!, 9;2, #VALUE!, #VALUE!;3, #VALUE!, #VALUE!;2, #VALUE!, #VALUE!;#VALUE!, 3, 3;#VALUE!, #VALUE!, #VALUE!;5, #VALUE!, #VALUE!;1, #VALUE!, 5}))

becomes

--({FALSE, FALSE, TRUE;FALSE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;FALSE, TRUE, TRUE;FALSE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, TRUE})

The MMULT function can't work with boolean values so we need to convert them into their numerical equivalents. TRUE - 1 annd FALSE - 0 (zero).

--({FALSE, FALSE, TRUE;FALSE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;FALSE, TRUE, TRUE;FALSE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, TRUE})

and returns

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

Step 3 - Sum values row-wise

MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1;1;1})

becomes

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

and returns

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

Step 4 - Convert non-numerical values to corresponding row numbers

The following IF function returns the row number if number is above 0 (zero), there are three strings that must match. FALSE returns "" (nothing).

IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})>0, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), "")

becomes

IF({1; 1; 1; 3; 0; 0; 3; 2; 1; 1; 1; 2; 0; 1; 2}>0, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), "")

becomes

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

and returns

{1;2;3;4; "";"";7; 8;9;10; 11;12; "";14;15}.

Step 5 - Extract k-th smallest value in array

The SMALL function makes sure that a new value is returned in each row.

SMALL(IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})>0, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""), ROWS($A$1:A1))

becomes

SMALL({1;2;3;4; "";"";7; 8;9;10; 11;12; "";14;15}, ROWS($A$1:A1))

The ROWS function returns a new number because the cell reference expands as the formula is copied to cells below.

SMALL({1;2;3;4; "";"";7; 8;9;10; 11;12; "";14;15}, ROWS($A$1:A1))

becomes

SMALL({1;2;3;4; "";"";7; 8;9;10; 11;12; "";14;15}, 1)

and returns 1.

Step 6 - Return value

The INDEX function returns a value based on a row and column number.

INDEX($B$3:$D$17, SMALL(IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})=3, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$D$17, 1, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$D$17, 1, 1)

and returns "Federer" in cell F8.

Get Excel *.xlsx file

multiple-criteria-lookup-with-multiple-unique-results-OR-LOGIC.xlsx

Weekly Blog EMAIL

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