Excel find word in matrix and return value of another row

Question

Hello!
I have a problem with an Excel formula.

I have a column of text. Each line should be checked if it contains a word from a matrix. If the word was found from the matrix, the value right of the found word should be inserted.

A2:Axxx is the Text.
B2:Bxxx are the returned value of Matrix

Matrix D3:E5

• First Column: Pattern
• Second Column: returned value

How can I do this?

rg
Hansi

Answer 1

I have found the solution:

=VERWEIS(2;1/SUCHEN($D$3:$D$5;A2);$E$3:$E$5)

Answer 2

Hi,

In cell B2, enter this formula

=XLOOKUP(TRUE,REGEXTEST(A2,$D$2:$D$4,1),$E$2:$E$4)

Hope this helps.