Search for exact string and return lookup value

[JayB0730]

Hello,

This has been racking my brain and my original formulas are pulling in the wrong values. Here is what I'd like to do...

• I have two Excel Tables; Table 1 where all my data exists. I would like to have a formula to determine if CATEGORY = A then I want to check if any LOOKUP words ( WHOLE WORDS ONLY) are found in the teh description--if so, return Table 2's SUB-CAT
• If not, just return "888".
• I need to lookup whole words so that in the example of "GI Joe", Sub-cat returns "789" because it found GI vs. "456" and ignoring "G"

Table 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]CATEGORY[/TD]
[TD]SUB-CAT[/TD]
[/TR]
[TR]
[TD]GI Joe[/TD]
[TD]A[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]I Love Coffee[/TD]
[TD]A[/TD]
[TD]Should return error to indicate no words found in description is found in Table 2 Lookup Words (this will alert me to add in the future)[/TD]
[/TR]
[TR]
[TD]pizza is great[/TD]
[TD]A[/TD]
[TD]000[/TD]
[/TR]
[TR]
[TD]Help Me![/TD]
[TD]C[/TD]
[TD]888[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]LOOKUP Words[/TD]
[TD]SUB CAT[/TD]
[/TR]
[TR]
[TD]Basketball[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]GI[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]PIZZA[/TD]
[TD]000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

How about:

Book1
	A	B	C
1	Table 1
2
3	DESCRIPTION	CATEGORY	SUB-CAT
4	GI Joe	A	789
5	I Love Coffee	A	No words found
6	pizza is great	A	0
7	Help Me!	C	888
8
9
10
11	Table 2
12	LOOKUP Words	SUB CAT
13	Basketball	123
14	G	456
15	GI	789
16	PIZZA	0
Sheet4

Cell Formulas
Range	Formula
C4	=IF(B4="A",IFERROR(LOOKUP(2^15,SEARCH(" "&$A$13:$A$16&" "," "&A4&" "),$B$13:$B$16),"No words found"),888)

 

[JayB0730]

How about:

	A	B	C
Table 1
DESCRIPTION	CATEGORY	SUB-CAT
GI Joe	A
I Love Coffee	A	No words found
pizza is great	A
Help Me!	C
Table 2
LOOKUP Words	SUB CAT
Basketball
G
GI
PIZZA

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]789[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]888[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]123[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]456[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]789[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IF(B4="A",IFERROR(LOOKUP(2^15,SEARCH(" "&$A$13:$A$16&" "," "&A4&" "),$B$13:$B$16),"No words found"),888)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Eric,

Thanks for your response! I'm interested in understanding the breakdown of your formula, specifically 2^15 --- What does this represent in this formula. I did the following and wondering if it would work out the same way? I created a helper column that normalized the DESCRIPTION Data. Formula I used was:

Worksheet Formulas[TABLE="width: 1055"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]HELPER CELL[/TH]
[TD="align: left"]=LOWER(
TRIM(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
DESCRIPTION CELL,
"("," "),
")"," "),
"-"," "),
":"," "),
";"," "),
"!"," "),
","," "),
"."," ")))
[/TD]
[/TR]
</tbody>[/TABLE]

Then, in the SUB-CAT field I used the following formula:

Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]SUB-CAT[/TH]
[TD="align: left"]=IF([@[CATEGORY]]="A",INDEX('LookupSheet.xlsx'!CATEGORY[CAT],MATCH(TRUE,ISNUMBER(SEARCH(" "&LookupSheet.xlsx'!CATEGORY[Categories]&" ",[@HELPER])),0)),[@[CATEGORY]])[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Well, let me explain how the LOOKUP part of the formula works, since it's a bit opaque. LOOKUP has some array processing functionality, so in the interior part, where it has this: " "&$A$13:$A$16&" " it creates an internal array. It has each of the words from A13:A16, plus it appends a space before and after, so you get this: {" Basketball "," G "," GI "," PIZZA "}. Now the SEARCH tries to find each of those words in " "&A4&" ". I put spaces before and after everything so that G would not match GI. So after all 4 SEARCHes have been done, the array looks like {#VALUE !,#VALUE !,1,#VALUE !} because only GI was found. So now we are trying to look up 2^15 from that array. The way LOOKUP works is, first, it ignores error values. Next, if it cannot find a match, it will return the last numerical value, or 1. SEARCH returns the position within the text where the search value is found. Since the maximum length of a text in a cell is 32768, if we search for 32768 (or 2^15 in exponential notation), we should get the matching position, or 3. Then LOOKUP returns the value from the second range $B$13:$B$16 in the # 3 position.

Hope that makes sense! I haven't actually tested your formula, but it seems it should work. The main difference is that in case of multiple matches, my formula will return the last match, and I believe yours will return the first match.