Forum Discussion
Search for multiple values
Hey guys,
I was wondering if anyone knows how you can search for multiple different values in an excel spreadsheet at once? I am attempting to search for multiple unique document numbers (BC997261, AE183488, etc.) to which I have the full names but wanted to know if there is a way to filter the spreadsheet to only include those values?
The data set I'm working with has ~40,000 rows so being able to filter for 10-20 or so at once rather than individually would be a huge help.
Thank you
2 Replies
The trick is to count the occurrences of each member of the document field of the main table within the shorter list of target documents.
= COUNTIFS(targetDocs, documentField)
That will return a 1 or 0 for each record, which can be used to filter the table
= FILTER( table, COUNTIFS(targetDocs, documentField) )
In the newest releases of Excel this can be presented in the form
= LET( include, COUNTIFS(targetDocs, documentField),
FILTER( table, include, "None found") )but you are unlikely to have such a version of Office 365.
In the absence of seeing your actual spreadsheet, or sample thereof, I can only suggest you try the FILTER function. You might have to play a bit with your criteria, using OR to allow for several possibilities.
If you can't resolve it, come back with a sample of the sheet itself so we don't have to create our own.
