How to get first record from lookup table that is a substring of some value?-CodePudding

In MS-EXCEL, how can I achieve something like below:

Given:
sheet1 - raw-info ('james brown england', 'tommy australia 1234', 'saka ireland', 'denmark martin')
sheet2 - countries (england, nigeria, usa, denmark, australia)

after applying some formula, I want a new column in sheet1 that will have the following values:

sheet1 with two columns:
raw-info ('james brown england', 'tommy australia 1234', 'saka ireland', 'denmark martin')
new-col (england, australia, **blank**, denmark)

logic is: if raw-info column value contains any value in countries column as a substring, use the matching value from countries column, else leave it blank.

CodePudding user response：

In B2(assuming you have headers):

=INDEX('Sheet2'!A:A,AGGREGATE(15,7,ROW('Sheet2'!$A$1:$A$6)/(ISNUMBER(SEARCH('Sheet2'!$A$1:$A$6,B2))),1))