Multiple wildcard lookups and include or exclude criteria
Hi Oscar,
This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. However I have been trying to modify the array formulas in the 3rd and 4th posts of that series to cope with wildcard searches or part cell matches and thought this post may help but not so far unfortunately.
I have tried numerous approaches but can't seem to get a multiple criteria series of "searches" (or counts) to work with anything but exact data.
I'm trying to search for part of a serial number e.g.(A110 within a string A110E12694369020 as one of my search inputs. In this case it is always the first 4 digits but I'd like to be able to input just A1 or *A1* into a search cell and not rely on reviewing the first 4 characters as this is not always the result for these serial numbers.
I'm also looking for items with corresponding dates equal or before a certain due date search string - your 4th post to Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john helped me resolve this criteria though.
Finally I'm trying another 'wildcard' search and to find FSS (or *FSS*) OR the exact inverse of this - so NOT containing FSS (or *FSS*) - and both of these search criteria are proving elusive to me.
All the data I have is in a large table but I'm not sure if I can alter it to be an Excel Table (which might help), as it is in a sheet that is deleted and replaced by an irregular macro from another workbook and this may corrupt a Table setup(?). It can not be a Pivot as the data is in a shared workbook and Pivot Tables do not update when in shared documents - hence the Array solution I am trying to develop. I have successfully named the columns of ranges I need though without loosing them on an update of data.
To summarise - I'd like to search by some form of wildcard; *app* to return a positive result for "apple" and to search for the opposite; have a criteria that would exclude "apple" using soemthing like *app*
Although I have around 80 columns I'm just looking to return the serial number in say Column A based on these multiple criteria; Col B <= a date, Col C does NOT contain a wildcard string *whatever*, Col D DOES contain a different wildcard string, Col E EXACTLY equals a third search string etc. I am happy to build up the criteria to more columns beyond this if I can resolve the NOT and the wildcard elements.
I'd really appreciate some further pointers, thanks Oscar!
Array formula in cell B9:
How to enter an array formula
- Copy above array formula
- Select cell B9
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell B9
- Copy cell B9 (Ctrl + c)
- Paste to cell range C9:E9
- Copy cell range B9:E9
- Paste to cell range B10:E20
Explaining the array formula in cell B9
Step 1 - Search for a text string
SEARCH($B$5, Sheet2!$A$2:$A$21)
Step 2 - Check if it is a number
ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21))
Step 3 - Check if cell B4 is "Include" or Exclude"
IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21))))
Step 4 - Add all arrays
((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")
Step 5 - Check if the number of criteria matches the array, if it does return the corresponding row number
IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")
Step 6 - Find k-th smallest row number
SMALL(IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1))
Step 7 - Return value from table
INDEX(Sheet2!$A$2:$D$21, SMALL(IF(COUNTA($B$5:$E$5)=((IF($B$4="Include", ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)), NOT(ISNUMBER(SEARCH($B$5, Sheet2!$A$2:$A$21)))))*($B$5<>"")+(IF($C$4="Include", ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)), NOT(ISNUMBER(SEARCH($C$5, Sheet2!$B$2:$B$21)))))*($C$5<>"")+(IF($D$4="Include", ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)), NOT(ISNUMBER(SEARCH($D$5, Sheet2!$C$2:$C$21)))))*($D$5<>"")+(IF($E$4="Include", ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)), NOT(ISNUMBER(SEARCH($E$5, Sheet2!$D$2:$D$21)))))*($E$5<>"")), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1)), COLUMN(A1))
Download excel *.xlsx file
Search for a text string 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 [โฆ]
Lookup with multiple criteria and return multiple search results
Question: How do I search a list containing First name column and a last name column? I want to search [โฆ]
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 [โฆ]
Search for multiple text strings in column โ AND logic
Question: How do I search a list for two text strings and return a list with where both strings occurs? [โฆ]
Search columns for a string and return records โ AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle [โฆ]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series [โฆ]
Search each column for a string each and return multiple records โ OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" [โฆ]
Row is returned if both search strings are found on the same row
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns [โฆ]
Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short [โฆ]
3 Responses to โMultiple wildcard lookups and include or exclude criteriaโ
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.
English
Spanish
Oh wow! Oscar you are amazing! That's absolutely brilliant and I can't wait to get into working through it and applying in my files to fully understand the details. That makes it so versatile now for me to utilise. I really don't know how to say thank you well enough. You're an absolute star and guru, thank you so much! There's beer (or whatever else you fancy) waiting for you in Oz one day! Such a fast response too, your website is an awesome resource Oscar, I've learnt so much!
Hi Oscar, I've had great progress assigning this solution for my file but I do have one more question if I may....how woudl you modify one of the Include/Exclude arrays to search for a BLANK cell. In particular I'm looking at a column of dates and want to make those with NO DATE an included search criteria. Cheers!
Dave,
Sorry, I donยดt know. The COUNTA function counts values in range B5:E5. A blank is not counted.