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.
>Solution :
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))