***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

Barry Houdini created this formula and I am hoping (he) or someone can help come up with a 2nd wild formula.

Barry's original formula:
=Lookup(2^15,SEARCH($D$2:$D$10,A2),($E$2:$E$10)

Example: Let's say i have the opportunity identifier in Column A, the product line in Column B, i need it to match up against a table that has the returned value in Column C which is the # product/service, but ONLY if the column A and B matches up.

Barry's formula only looks for the Opportunity identifier column but can't match up against the other column headers for a match. I need to copy the formula across all the cells.

Any help is appreciated!!!!

Sheet 1 Cross Tab:
Column A is the opportunity Identifier
<TABLE style="WIDTH: 443pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=591 border=0><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376; mso-outline-parent: collapsed" width=147><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 43.5pt; mso-height-source: userset" height=58><TD class=xl75 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 110pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 43.5pt; BACKGROUND-COLOR: silver" width=147 height=58>Opportunity Identifier</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 71pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=95>Network Services</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 70pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=93>Storage Networking</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=64>Storage Supplies</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=64>STORAGE SUPPORT</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=64>PSG SUPPORT</TD><TD class=xl76 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=64>Replacement Parts</TD></TR><TR style="HEIGHT: 42.75pt; mso-height-source: userset; mso-outline-parent: collapsed" height=57><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 110pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 42.75pt; BACKGROUND-COLOR: transparent" align=left width=147 height=57>CRMEM1-2-197SPF3</TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>=LOOKUP(2^15,SEARCH(Product!$A$2:$A$11894,$AE18),Product!$C$2:$C$65000)</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 110pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 28.5pt; BACKGROUND-COLOR: transparent" align=left width=147 height=38>CRMEM1-2-11KRLH7</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 110pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 24pt; BACKGROUND-COLOR: transparent" align=left width=147 height=32>CRMEM1-2-ZYNP3O</TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>


Sheet 2 Product is the Look Up Table:
<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=436 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 7972" width=218><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl69 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 93pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ff8080" width=124 height=22>Opportunity Identifier</TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 164pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=218>Product Line Name</TD><TD class=xl69 dir=ltr style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 71pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ff8080" width=94># Product/Service</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>CRMAM1-3-11KEY65</TD><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">Network Services</TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>CRMAM1-3-13NHDDI</TD><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">STORAGE SUPPORT</TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>CRMAM1-3-13NHDDI</TD><TD class=xl70 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">EVA Disk & SW</TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: transparent">1</TD></TR></TBODY></TABLE>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi everyone,
It's me again. The above problem was solved by Aladin and I truly thank him for his help. However, I have another problem and it relates to the original function that Barry Houdini wrote.

I used his Lookup formula and it works BEAUTIFULLY; however, there is a quirk somewhere that I am not sure if it's me or the formula.

=LOOKUP(2^15,SEARCH(Category!$A$2:$A$167,J4),Category!$B$2:$B$167)

The above formula looks up the words in my category and applies it correctly. However, some key words that it's supposed to look for doesn't get applied but instead another category is chosen.

The below description SHOULD return "Mission Critical" however, it returns "Attach" and there's no words or characters that I can see is related to "Attach" at all. What's also strange is in most of my query, the "mission critical" returns the correct value...it's just some descriptions that do not.

Any help is GREATLY appreciated and I thank you in advance if you can help solve this problem.

here's the Look up in Column J4
1st example: AM TS Mission Critical Healthcare Express; US Q409-Q110
2nd example: GLobal TS MCS Healthcare Exp; AM CA Q409 Mail SRE and Telemarketing

Key word is A2:A167
Cateogry is B2:B167

<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=232 border=0><COLGROUP><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl204 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 126pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: yellow" align=left width=168 height=22>Key Word</TD><TD class=xl204 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=left width=64>Category</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>MC</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Mission Critical</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Maximize</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Maximise</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Investment</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Sustain</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Simulation</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Discovery</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Blade</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>MCS Healthcare</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Improving</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Proactive</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Select</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>P24</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>MCS</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Attach</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Weather</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Weathering</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Economic Storm</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>ECO</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>ECOStorm</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>CarePack</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Care</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Pack</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Just for You</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Unexpected Risks</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Protect</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Extend</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Storage Warranty</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Do it Right</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>ConvCarePack</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>TSPack</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=left height=22>Post</TD><TD class=xl206 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>Attach</TD></TR></TBODY></TABLE>
 
I believe it would have been more appropriate to post this at Excel Questions... That said, try:

Define Lrow by means of Insert|Name|Define as referring to:

=MATCH(REPT("z",255),Category!$A:$A)

Define KEYWORDS as referring to:

=Category!$A$2:INDEX(Category!$A:$A,Lrow)

Define CATEGORIES as referring to:

=Category!$B$2:INDEX(Category!$B:$B,Lrow)

Now you can invoke:

=LOOKUP(9.99999999999999E+307,SEARCH(KEYWORDS,J4),CATEGORIES)


trankim said:
Hi everyone,
It's me again. The above problem was solved by Aladin and I truly thank him for his help. However, I have another problem and it relates to the original function that Barry Houdini wrote.

I used his Lookup formula and it works BEAUTIFULLY; however, there is a quirk somewhere that I am not sure if it's me or the formula.

=LOOKUP(2^15,SEARCH(Category!$A$2:$A$167,J4),Category!$B$2:$B$167)

The above formula looks up the words in my category and applies it correctly. However, some key words that it's supposed to look for doesn't get applied but instead another category is chosen.

The below description SHOULD return "Mission Critical" however, it returns "Attach" and there's no words or characters that I can see is related to "Attach" at all. What's also strange is in most of my query, the "mission critical" returns the correct value...it's just some descriptions that do not.

Any help is GREATLY appreciated and I thank you in advance if you can help solve this problem.

here's the Look up in Column J4
1st example: AM TS Mission Critical Healthcare Express; US Q409-Q110
2nd example: GLobal TS MCS Healthcare Exp; AM CA Q409 Mail SRE and Telemarketing

Key word is A2:A167
Cateogry is B2:B167

<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=232><COLGROUP><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 126pt; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl204 height=22 width=168 align=left>Key Word</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl204 width=64 align=left>Category</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>MC</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Mission Critical</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Maximize</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Maximise</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Investment</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Sustain</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Simulation</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Discovery</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Blade</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>MCS Healthcare</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Improving</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Proactive</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Select</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>P24</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>MCS</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Mission Critical</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Attach</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Weather</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Weathering</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Economic Storm</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>ECO</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>ECOStorm</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>CarePack</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Care</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Pack</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Just for You</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Unexpected Risks</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Protect</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Extend</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Storage Warranty</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Do it Right</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>ConvCarePack</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>TSPack</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 height=22 align=left>Post</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl206 align=left>Attach</TD></TR></TBODY></TABLE>
Click to expand...
 
Thank you Aladin for your response. I truly apologize for posting it here. You are absolutely right that I should have posted it in the Questions section, but my original intent was that I thought it was an easier trail to follow since my problem was similiar to the challenge. I will take my request and post it to the correct forum as you suggested.

That said, the formula you gave worked the same way as the one I used from Barry Houdini. It still returned the "Attach" vs "Mission Critical", but hopefully you can respond to any other suggestions when I post it in the correct forum.

New subject title will be "Fuzzy Match using Index or Search"
 
trankim said:
Thank you Aladin for your response. I truly apologize for posting it here. You are absolutely right that I should have posted it in the Questions section, but my original intent was that I thought it was an easier trail to follow since my problem was similiar to the challenge. I will take my request and post it to the correct forum as you suggested.

That said, the formula you gave worked the same way as the one I used from Barry Houdini. It still returned the "Attach" vs "Mission Critical", but hopefully you can respond to any other suggestions when I post it in the correct forum.

New subject title will be "Fuzzy Match using Index or Search"
Click to expand...

Trankim,

Have a closer look at KEYWORDS: You'll see that the formula succeeds also to find the string care in J4 and in the other example, whose associated CATEGORY is 'Attach'.

I propose the following modification...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&KEYWORDS&" "," "&J4&" "),CATEGORIES)
 
Last edited:
Re: June/July 2008 Challenge of the Month

Copied from Excel Help:)
Code: 
Sub FindColor()
    FinalRowD = Cells(1, 4).End(xlDown).Row
    For i = 2 To FinalRowD
        With Worksheets(1).Range("RngPhrases")
            Set c = .Find(Cells(i, 4).Value, LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Offset(, 1).Value = Cells(i, 5).Value
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    Next i
End Sub
"RngPhrases" is dynamically defined as "=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)" in the Name Manager
 
Last edited by a moderator:
Guys and Gals,

This message does not belong here, and I wasn't sure where exactly it belongs, but for all of you XL-challengees out there, here's a new one:

Dare make the following chart with a Y-axis gap:

1jbm96

http://twitpic.com/1jbm96

[Creative XL Charts]

PS: I am still on 2003, maybe 2007 has more efficient chart func's
 
Here's my UDF macro for the challange it will locate the color in the line and find the correct person and display it in the cell. This will work with a large table.
Code: 
Function MYTEST(MYSTRINGBLI) 'GET THE NAME ASSOCIATED WITH THE COLOR
MYTEST = Application.WorksheetFunction _
        .Index(Worksheets(1).Range("D2:E100"), Application.WorksheetFunction.Match(MYSTRINGBLI, Worksheets(1).Range("D2:D100"), True), 2)
End Function
Function MYTEST2(MYSTRINGILB) 'SHOW THE COLOR SO I CAN COMPARE IT
MYTEST2 = Application.WorksheetFunction _
        .Index(Worksheets(1).Range("D2:E100"), Application.WorksheetFunction.Match(MYSTRINGILB, Worksheets(1).Range("D2:D100"), 1), 1)
End Function
Function MYRESULT(MYSTRING) ' 5-24-10 WORKS TO FIND NAME ASSOCIATE TO COLOR IN LINE OF TEXT.
HOLDSTRING = MYSTRING
Dim MYHOLDSTRING As String
CNT = 1
WLEN = 0
HMB = 1
DONE = 1
HOLDNUM = 0
Do Until DONE = 0
    HOLDCHR = Mid(HOLDSTRING, CNT, 1)
    If ((HOLDCHR < Chr(65) Or HOLDCHR > Chr(122)) Or (HOLDCHR > Chr(90) And HOLDCHR < Chr(97))) Then
        If HMB > 1 Then
            WLEN = HMB
        End If
        MYRESULT = "NOT ASSIGNED"
        If MYTEST2(Mid(HOLDSTRING, CNT - WLEN, WLEN)) = Mid(HOLDSTRING, CNT - WLEN, WLEN) Then
            MYRESULT = MYTEST(Mid(HOLDSTRING, CNT - WLEN, WLEN))
            DONE = 0
        Else
            WLEN = 0
            MYRESULT = "NOT AVAILABLE"
        End If
    Else
        WLEN = WLEN + 1 'INCREMENT WORD LENGTH
        HMB = 0
    End If
If CNT > Len(HOLDSTRING) Then
    DONE = 0
End If
CNT = CNT + 1
HMB = HMB + 1
Loop
End Function
Enter into cell B2 the formula as follows
=MYRESULT(A2)

The macro function will find the color and match it no matter were it is in the line.
 
Last edited by a moderator:
Re: June/July 2008 Challenge of the Month

barry houdini said:
Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)
Click to expand...

That is genius but I don't understand how it works... can someone explain to my simple brain what this does?
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top