Excel - search on multiple key words

Copper Contributor

When I use the general search function (ctrl-F) to search for text and/or numbers in the cells of a column, I would like to use multiple search terms that a field might contain, but not necessarily in the correct sequence. 

For instance, I have a column with 2000 rows that contain a description of items I bought.

I want to search for a 'Silver bracelet with blue stones'. When I search for 'Silver bracelet, blue stones', it will not find it, because the text doesn't exactly match the original text. I would like to search on keywords, so:  'Silver' & 'Blue' & 'Bracelet', but Excel doesn't accept this. 

At this stage I just want to look up certain data in my spreadsheet, like the associated price. I don't need to do anything with it (yet).

Any help would be much appreciated!

3 Replies

@LeonE57 

=AND(ISNUMBER(SEARCH("silver",B4)),ISNUMBER(SEARCH("blue",B4)),ISNUMBER(SEARCH("bracelet",B4)))

Maybe with this formula. The formula checks if all search criteria are within the string. It returns either TRUE or FALSE (WAHR or FALSCH in the attached sheet which is in german). 

@OliverScheurich 

Thank you very much for your quick reply.
I agree that this should work, but I wondered if there is a way to use the 'AND' criteria within the standard search option with CTRL-F.
I would like to be able to quickly look up an item by using CTRL-F and typing a few keywords.
If that's not possible, I will use your option and add an extra TRUE/FALSE column and put an extra row at the top of my spreadsheet where I can type the search criteria (e.g. in cells A1 - A4.

So where you typed "silver", "blue" and "bracelet", I would refer then to cells A1, A2, A3 and A4 (for 4 search criteria) and enter those criteria in these cells.

I just hoped there would be an easier way within the CTRL-F option.

@LeonE57 Afraid Ctrl+F doesn't work such way