Search each column for a string each and return multiple records

Search each column for a string each and return multiple records – OR logic

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

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

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

How to copy array formula

Copy (Ctrl + c) cell E9
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).

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

Partial match 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 […]

Partial match with two conditions and return multiple results
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

Comments (5)

5 Responses to “Search each column for a string each and return multiple records – OR logic”

Pat says:

December 6, 2013 at 7:03 pm

Hi Oscar,
Thanks for creating such a helpful website and I've a question if I would like to return the value with a prefix order would it possible? If not can I just add another column in the data and used it as part of the search criteria?

Reply
- Oscar says:
  
  December 9, 2013 at 12:42 pm
  
  Pat,
  
  Thank you!
  Can you describe your problem in greater detail?
- Pat says:
  
  December 10, 2013 at 4:16 am
  
  Hi Oscar,
  Thanks you very much for taking your time to reply, apologies for not explain in much details in my previous post. Basically, I’m working on a scrap form whereby this scrap form is tie to a BOM (as shown per : https://s8.postimg.org/6fjw91ef9/Pat_Data_Table.png ). The scrap form have 2 section;
  
  1. Sub-Assembly
  2. Raw Components
  
  When user select the Finishing Good part no from the drop down box, the Sub-assembly and Raw components will be display (show as per https://s15.postimg.org/zfsuxvprv/Pat.png) but this result display was not in the sort order list that I required. I need the return result in the sorting order this is because user will used this data to transact into another system.
  
  P/S : The BOM list (the sorting order) is based on system data.
  
  Once again really appreciate your help.
  
  Best Regards,
  Pat
- Oscar says:
  
  December 11, 2013 at 3:24 pm
  
  Pat,
  
  Can you provide a workbook?
- Oscar says:
  
  December 13, 2013 at 10:19 am
  
  Pat,
  
  read this post:
  Lookup and return multiple values sorted in a custom order

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form