Match multiple values in substring and use specific category

Question

I am having a string and I am trying to match multiple values for this string.

I think I figured out how to match the string, BUT haven't yet figured out to get the wanted value in the cell.

For matching I am using:

=sumproduct(isnumber(search(transpose($B$17:$B$20),A2)))

Below is my example:

This is an example sheet:

https://docs.google.com/spreadsheets/d/1IN2hMcpEc1sZ7JGP3XGD3AJMYnKt2RlsLIlvk8vXtbo/edit?usp=sharing

How can I use the value from the matching table?

I appreciate you replies!

Answer 1

try:

=INDEX(IFNA(VLOOKUP(REGEXEXTRACT(PROPER(A2:A9), 
 TEXTJOIN("|", 1, B13:B16)), B13:C16, 2, )))