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 (BC9972...
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.