To fuzzy or not to fuzzy? Thats my question...

[MikeMikeMike]

Ok so I started a four page thread not realizing what it was going to turn into...I am glad it did as I am now more educated because of it. However I still did not really get my question answered or the formula I need so I decided to repost and ask simply if what I am doing is possible without having to get fuzzy with it.

First the thread I started that has all kinds of solutions...none of which work for me:

http://www.mrexcel.com/forum/showthread.php?t=529356

The closest I got to what I am looking for is this:

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4) (----And the one used against my below example data----)

However when adding a new row (and making the needed adjustments to the formula to take into account the new row) ...it overwrites the previous row if both terms or contained in a*.

So for example:

Sheet 1 (Where the formula exists)
<table border="0" cellpadding="0" cellspacing="0" width="344"><col style="width: 181pt;" width="241"> <col style="width: 77pt;" width="103"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 181pt;" height="20" width="241">Keyword</td> <td class="xl65" style="width: 77pt;" width="103">Cat</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">tele com</td> <td align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecomm</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunication</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecomunication</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunications</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunication jobs</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom mobile</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom jobs</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom voip
</td> <td align="center">Telecom
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">british telecom</td> <td>Telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Notice how Telecom is overwriting the other categories...which makes sense but makes this formula useless for this purpose.
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td><td>
</td></tr></tbody></table>Sheet 2

<table border="0" cellpadding="0" cellspacing="0" width="195"><col style="width: 98pt;" width="131"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 98pt;" height="20" width="131">Phrase</td> <td class="xl63" style="width: 48pt;" width="64">Category</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">carreers</td> <td>Jobs</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">job</td> <td>Jobs</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">voip</td> <td>VOIP</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telep</td> <td>Equipment</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom</td> <td>Telecom</td> </tr> </tbody></table>

So finally (thank you for anyone that has read this far) my questions:

1. How can I work precedence into the picture?

2. Can this be done with a match + contains + precedence in some way?

3. Is a fuzzylookup or fuzzymatch my ONLY answer? (Which one?)

4. And last but not least - if getting fuzzy is the only answer then can one of you masters help bring me up to speed? (I am aware of this link:
http://www.mrexcel.com/forum/showthread.php?t=195635 and was told in my post to "try figuring it out" which I will indeed figure it out but with time pressing down on me I am hoping that I can at least get clear direction as to what I should focus on as the post that I started yesterday ended up out of hand and as a result didn't get me to where I need to be....closer but not there yet.)

1000 Thank You's to All of you!

-Mike

 

[Jon von der Heyden]

Hi Mike

Per the forum rules, you need to stick with your original thread.

Threads of duplicate nature will be locked or deleted.