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 […]
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
Question: How do i create a flexible search formula to search a list? Answer: The following formula letĀ“s you search […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
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