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

DESCRIPTION	CATEGORY	SUB-CAT
GI Joe	A	789
I Love Coffee	A	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)
pizza is great	A	000
Help Me!	C	888

<tbody>
</tbody>

Table 2

LOOKUP Words	SUB CAT
Basketball	123
G	456
GI	789
PIZZA	000

<tbody>
</tbody>

 

[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
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

<tbody>
</tbody>

Sheet4​

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

<tbody>
</tbody>

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

Cell	Formula
HELPER CELL	=LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( DESCRIPTION CELL, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))

<tbody>
</tbody>

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

Worksheet Formulas

Cell	Formula
SUB-CAT	=IF([@[CATEGORY]]="A",INDEX('LookupSheet.xlsx'!CATEGORY[CAT],MATCH(TRUE,ISNUMBER(SEARCH(" "&LookupSheet.xlsx'!CATEGORY[Categories]&" ",[@HELPER])),0)),[@[CATEGORY]])

<tbody>
</tbody>

 

[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.