Forum Discussion

LeonE57's avatar
LeonE57
Copper Contributor
Apr 26, 2022

Excel - search on multiple key words

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 i...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 26, 2022

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

  • LeonE57's avatar
    LeonE57
    Copper Contributor
    Apr 26, 2022

    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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 26, 2022

      LeonE57 Afraid Ctrl+F doesn't work such way

Resources