Excel - VLOOKUP return based on partial match-CodePudding

I have two sheets. Sheet 2 contains keywords. Sheet 1 contains some very long strings. Among the long strings, I need to find if there is match from the keyword on other page sheet 2.

Sheet 1:

Sheet1

Sheet 2

Sheet2

I used Wildcard but retuning #N/A

=VLOOKUP("*"&A2&"*",Sheet2!A:A,1,FALSE)

CodePudding user response：

If you have a list of keywords, and you want to return the one that matches, then try:

=IFERROR(INDEX(KeyWords,AGGREGATE(15,6,1/ISNUMBER(FIND(KeyWords,A2))*ROW(KeyWords),1)),"")

CodePudding user response：

Try SEARCH() and FILTER().

=TEXTJOIN(", ",1,FILTER(Sheet2!$A$1:$A$3,ISNUMBER(SEARCH(Sheet2!$A$1:$A$3,A2))))

TEXTJOIN() for joining multiple keyword if any.

CodePudding user response：

Perhaps you could try using the LOOKUP() Function

• Formula used in cell B2

=LOOKUP(9^9,SEARCH($D$1,A2),$D$1)