Search for multiple values

%3CLINGO-SUB%20id%3D%22lingo-sub-1272840%22%20slang%3D%22en-US%22%3ESearch%20for%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272840%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20anyone%20knows%20how%20you%20can%20search%20for%20multiple%20different%20values%20in%20an%20excel%20spreadsheet%20at%20once%3F%20I%20am%20attempting%20to%20search%20for%20multiple%20unique%20document%20numbers%20(BC997261%2C%20AE183488%2C%20etc.)%20to%20which%20I%20have%20the%20full%20names%20but%20wanted%20to%20know%20if%20there%20is%20a%20way%20to%20filter%20the%20spreadsheet%20to%20only%20include%20those%20values%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20set%20I'm%20working%20with%20has%20~40%2C000%20rows%20so%20being%20able%20to%20filter%20for%2010-20%20or%20so%20at%20once%20rather%20than%20individually%20would%20be%20a%20huge%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1272840%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272863%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20multiple%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605269%22%20target%3D%22_blank%22%3E%40JKLOSS48%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20absence%20of%20seeing%20your%20actual%20spreadsheet%2C%20or%20sample%20thereof%2C%20I%20can%20only%20suggest%20you%20try%20the%20FILTER%20function.%20You%20might%20have%20to%20play%20a%20bit%20with%20your%20criteria%2C%20using%20OR%20to%20allow%20for%20several%20possibilities.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can't%20resolve%20it%2C%20come%20back%20with%20a%20sample%20of%20the%20sheet%20itself%20so%20we%20don't%20have%20to%20create%20our%20own.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

@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.

Highlighted

@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.