CODEWITHSUNDEEP
excelfilter
Han
Han

Reputation: 35

Advanced Filtering in Excel, Search Multiple Text Conditions in 1 column

I have two columns similar to this and want to search for both "pineapple" and the "ana" to get the result Kate. I have attempted to use advanced search but found this will get all 3 of these names if I do or don't use the wildcard symbol *. I have also tried the filter function available in Excel but cannot filter based on multiple AND (I think it's AND?) criteria with the wildcard.

Name Basket
Sam Apple, Banana
Jim Apple, Pineapple, Bread
Kate Pineapple, bread, Banana

I also have this data available in a table format where each name and item is on a separate line rather than on the same line with commas

Is there a way I can filter my data based on multiple AND criteria in a single column with or without the use of the wildcard? Maybe searching "pineapple" and "banana" to get the result Kate?

Cheers

Upvotes: 1

Views: 837

Answers (1)

Harun24hr
Harun24hr

Reputation: 36850

Try FILTER() like below-

=FILTER(A2:A4,ISNUMBER(SEARCH("Pineapple",B2:B4))*(ISNUMBER(SEARCH("ana",B2:B4))))

enter image description here

Upvotes: 2

Related Questions