Search for multiple values

Copper Contributor

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

@JKLOSS48 

 

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.

@JKLOSS48 

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.