VLOOKUP maybe? Search a row of text in one worksheet by comparing from another worksheet

[RoseM]

Hi,

I have worksheet1 with rows that contain a sentence. In another worksheet2 I have a table with Keywords that I want to look up in worksheet1 and then redefine them with worksheet2 table. I thought perhaps it would be an IF with VLOOKUP, but I'm not getting the expected result.

For example:


I want to be able to read Row 1(Summary column); compare all the words in Summary to table in Worksheet2(column1-Keyword), then replace with the Defined Value from Worksheet2 to Worksheet1 Catergory. I've tried Vlookup, contain, search, and nothing is working. Any assistance is greatly appreciated.

Worksheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Summary[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]LCD is blank[/TD]
[TD]Screen[/TD]
[/TR]
[TR]
[TD]Reader is not turning on[/TD]
[TD]Card Reader[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Defined Value[/TD]
[/TR]
[TR]
[TD]LCD[/TD]
[TD]Screen[/TD]
[/TR]
[TR]
[TD]Reader[/TD]
[TD]Card Reader[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[DRSteele]

Welcome to the forum.

I considered that more than one Keyword could exist in each Summary.

You might need to update your MSOffice software for function TEXTJOIN to work. You might also need to enter it with CTRL+Shift+Enter.

Copy B4 downwards.

Book1
	A	B
1	Worksheet1
2		Office 365
3	Summary	Category
4	LCD is blank	Screen
5	Reader is not turning on	Card Reader
6	LCD wrecked the Reader	Screen, Card Reader
7	Paperback writer on LCD	Screen, Pencil
8	Other stuff
9
10	Worksheet2
11
12	Keyword	Defined Value
13	LCD	Screen
14	Reader	Card Reader
15	Writer	Pencil
16	Printer	Paper
Sheet39

Cell Formulas
Range	Formula
B4	=TEXTJOIN(", ",,IF(IFERROR(SEARCH($A$13:$A$16,A4),0),$B$13:$B$16,""))

 

[Aladin Akyurek]

In B2 of Sheet1 enter and copy down to B3...

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet2!$A$2:$A$3&" "," "&$A2&" "),Sheet2!$B$2:$B$3),"")

 

[RoseM]

Thank you Aladin! This worked. Now I have both options from you and DRSteele. It is truly appreciated.

 

[Aladin Akyurek]

Thank you Aladin! This worked. Now I have both options from you and DRSteele. It is truly appreciated.

You are welcome.