how to lookup a key word in multiple sentences i.e. array and then return a number that is next to t-CodePudding

Hello Stack Overflow community please help me with the problem below

how to lookup a key word in multiple sentences i.e., array and then return a number that is next to the original key word list of lookup words.

we have 3 arrays

List of what words to Find List of sentences to find the original word and list of numbers that are related to the original searched word.

The number is to be returned to the same row as the original sentance that coresponds to the original searched word.

Regards Troy

Variour attems to get some sort of result.

=INDEX(A2:A12,MATCH(D2:A12,D2:D12,))

=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)))

=IF($D2="","",(INDEX({B2:B12,""},MATCH(TRUE,ISNUMBER(SEARCH({D2:D12"," ",""},D2)),0))))

=INDEX(B2:B12,MATCH(1,(A2:A12=D2)*(D2:D12=D2),0))

=VLOOKUP(B2,A2:A12,2,0)

=INDEX(B2:B12,MATCH(D2,A2:A12,))

CodePudding user response：

Perhaps you could try in this way,

• Formula used in cell F2

=INDEX($B$2:$B$11,MATCH(TRUE,ISNUMBER(SEARCH($D2,$A$2:$A$11)),0))

If you have access to MS365, then use XLOOKUP()

• Formula used in cell F2

=XLOOKUP(TRUE,ISNUMBER(SEARCH(D2,$A$2:$A$11)),$B$2:$B$11,"")

You can also use a LOOKUP() Function

• Formula used in cell F2

=LOOKUP(9^9,SEARCH(D2,$A$2:$A$11),$B$2:$B$11)

Much better way is using WildCard With XLOOKUP() function

• Formula used in cell F2

=XLOOKUP("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,"",2)

With VLOOKUP() & wildcard as well,

• Formula used in cell F2

=VLOOKUP("*"&D2&"*",$A$2:$B$11,2,0)