I am trying to use a list of keywords in sheetB ColumnB to search strings in sheetA ColumnB.
If the string has a match to the keywords, I would like to return the value of sheet B column D into the Sheet A column G
I have tried this one:
=map(B2:B,lambda(Σ,if(Σ="",,ifna(regexextract(Σ,"(?i)"&textjoin("|",1,SheetB!B2:B))))))
This formula is extremely close, it find the matches, but it does not cross over the value I need from SheetB ColumnD in place of the match.
Example google sheet:
https://docs.google.com/spreadsheets/d/1qH_pXfHttFmmpr_1ve7TrRQcRzU9fFcfealMJrxm21s/edit?usp=sharing
I will keep this sheet active forever to help others.
As always, I really appreciate all of the help from Stack Exchange. I absolutely love this community!
>Solution :
Add xlookup to the existing function:
=map(B2:B,lambda(Σ,if(Σ="",,xlookup(ifna(regexextract(Σ,"(?i)"&textjoin("|",1,SheetB!B2:B))),SheetB!B:B,SheetB!D:D,))))