Search each column for a string each and return multiple records – OR logic
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:
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).
--({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
Search and return multiple values category
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Excel categories
5 Responses to “Search each column for a string each and return multiple records – OR logic”
Leave a Reply
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.
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?
Pat,
Thank you!
Can you describe your problem in greater detail?
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
Pat,
Can you provide a workbook?
Pat,
read this post:
Lookup and return multiple values sorted in a custom order