I'm looking for help in finding the proper conditional formatting formula to highlight the cell if it contains a word found in a list I've made in another column. I'd like this list to be dynamic where I will add in more words in the future.

Example:

Column A (conditional formatting to apply)------------Column B (dynamic list)

2015 AUDI Q7 (*highlight)------------------------------------------------Transit

2021 HYUNDAI ELANTRA ---------------------------------------Rogue

2019 FORD ESCAPE -------------------------------------------Q7

2019 FORD TRANSIT VAN (*highlight) -------------------------------------etc, etc

2014 CADILLAC ESCALADE

2018 CHRYSLER PACIFICA

2019 NISSAN ROGUE SPORT (*highlight)

2018 JEEP GRAND CHEROKEE

So within Column A, I'd like for '2015 Audi Q7', '2019 Ford Transit Van', and '2019 Nissan Rogue Sport' to be highlighted because they contain a word found in the Column B list. And if I were to add to the list in the future, it should automatically highlight the cell in Column A if applicable (i.e. if I were to add 'Escalade' to the list, then '2014 Cadillac Escalade' in Column A would be highlighted).

Thanks!

CodePudding user response：

Try:

=regexmatch(lower($A1),lower(textjoin("|",1,$B:$B)))