In Excel 365 I use the following formula and a dynamic search bar (anchored to cell B2) which retrieves information from a data table (two columns, Question and Answer) on a separate sheet (Table1) and shows it below the search bar:

=FILTER(Table1,ISNUMBER(SEARCH($B$2,Question)),””)

This works fine for single keywords but ideally, I would like to able to input multiple partial keywords into the search bar and any hits would display below.

For example, currently if I search for ‘how many people’, I get a single result (“How many people are there in the program?”) that contains the exact string, but strings containing the words ‘how’, ‘many’, and ‘people’ in different positions are not retrieved (“Many people are wondering how to do it”, or “Richard Manyon showed the peoples of the world his idea”)

Note: I don’t need the text to be highlighted or marked up in any way, just for any retrieved data to show beneath the search bar as it does currently for single keyword input.

Many thanks in advance for anyone that can assist.