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!