Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike · Feb 15, 2011

I am struggling with this and wondering if you could help..

Worksheet 1 Column A & B
Keyword Category
telecom Telecom
tele com #N/A

Worksheet 2 Column A & B
term category
telecom Telecom
telcom Telecom
tele com Telecom

What I want to do is pull the category name from worksheet 2 on to worksheet 1.

=VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)

Is not working...any ideas? I am not dealing with any duplicates so that is no a worry...

MikeMikeMike · Feb 17, 2011

@anyone..using something that uses a specific word to filter does not work as it is not scalable.

@ al_b_cnu (or anyone else) - Thank you! I got it working but am dealing with the same problem which is words that have more letters taken precedence over words that have fewer letters. Basically meaning this:

regardless of the order...when the word jobs (4 letters) is in the same cell as telecom (7 letters) the result is not what I need. It displays "Telecom" in B2 instead of "Jobs".

Is there anyway around this at all? What would be the options. I understand that the fuzzy lookup is looking for a percentage close to that specified on sheet2 a2....is that the ONLY option?

Why in the world not a simple CONTAINS???? I mean I love what I am learning here guys...I can see and know it will come in extremely handy in the near future.

However - I am starting to believe that what is being suggested (fuzzy) - thus far - is too prone for error for this specific need?

al_b_cnu · Feb 18, 2011

Maybe a simple UDF like this will do it?

Code:

Option Explicit

Function GetCategory(ByVal SearchString As String, ByVal LookupTable As Range, ByVal IndexNum As Integer) As Variant
Dim rCur As Range
Dim sCur As String

SearchString = LCase$(WorksheetFunction.Trim(LCase$(SearchString)))

For Each rCur In Intersect(LookupTable.Resize(, 1), Sheets(LookupTable.Parent.Name).UsedRange)
    sCur = LCase$(WorksheetFunction.Trim(CStr(rCur.Value)))
    If InStr(SearchString, sCur) <> 0 Then
        If IndexNum = 0 Then
            GetCategory = rCur.Row
        Else
            GetCategory = rCur.Offset(, IndexNum - 1).Value
        End If
        Exit Function
    End If
Next rCur
GetCategory = CVErr(xlErrNA)
End Function

Lookup table:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Phrase</td><td style="font-weight: bold;;">Category</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">careers</td><td style=";">Cat 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">job</td><td style=";">Cat 2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">voip</td><td style=";">Cat 3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">telep</td><td style=";">Cat 4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">jobs</td><td style=";">Cat 5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Mobile</td><td style=";">Cat 6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">telecom</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">tele com</td><td style=";">Cat 8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

Formula:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Keyword</td><td style="font-weight: bold;;">Cat</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">telecom</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">tele com</td><td style=";">Cat 8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">telecomm</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">telecommunication</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">telecomunication</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">telecommunications</td><td style=";">Cat 7</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">telecommunication jobs</td><td style=";">Cat 2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">telecom mobile</td><td style=";">Cat 6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">telecom jobs</td><td style=";">Cat 2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">telecom voip</td><td style=";">Cat 3</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">british Telecom</td><td style=";">Cat 7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=GetCategory(<font color="Blue">A2,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=GetCategory(<font color="Blue">A3,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=GetCategory(<font color="Blue">A4,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=GetCategory(<font color="Blue">A5,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=GetCategory(<font color="Blue">A6,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=GetCategory(<font color="Blue">A7,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=GetCategory(<font color="Blue">A8,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=GetCategory(<font color="Blue">A9,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=GetCategory(<font color="Blue">A10,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=GetCategory(<font color="Blue">A11,Sheet2!A:B,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=GetCategory(<font color="Blue">A12,Sheet2!A:B,2</font>)</td></tr></tbody></table></td></tr></table><br />

MikeMikeMike · Feb 18, 2011

I love you!

al_b_cnu · Feb 18, 2011

MikeMikeMike said:
I love you!

Careful - what would the wife say

Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike

New Member

MikeMikeMike

New Member

al_b_cnu

Well-known Member

MikeMikeMike

New Member

al_b_cnu

Well-known Member

Similar threads

Share this page

Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike

New Member

MikeMikeMike

New Member

al_b_cnu

Well-known Member

MikeMikeMike

New Member

al_b_cnu

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock