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

Roseus · Feb 15, 2011

I'm sorry, that's totally my bad. I meant:

=VLOOKUP(A3,Sheet2!A1:B4,2,FALSE)

I put this in the cell you have marked "N/A".

If MrKowz is right about needing the FuzzyLookup, this won't help. But I don't understand why you need that level of complexity for a very simple lookup, unless I don't understand your request.

Good luck!

MikeMikeMike · Feb 15, 2011

no that doesnt work either...I think the other guy had the right idea about fuzzy lookups. Sounds awesome, just dont see me getting it done in time for my meeting.

Any idea where I can have a fuzzylookup written?

Aladin Akyurek · Feb 15, 2011

MikeMikeMike said:
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...

Sheet1, B2, copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

MikeMikeMike · Feb 15, 2011

Whats odd is that this is ALMOST IT...and its not all that complicated.

Thoughts?

http://www.youtube.com/watch?v=bKmxFhOY70M

danzon · Feb 15, 2011

MikeMikeMike said:
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...

Based on the sample data that you have posted above...

this should work on Sheet 1 in cell B2...

=VLOOKUP(A2,Sheet2!A:B,2,0)

MikeMikeMike · Feb 15, 2011

Yeah that does work...but its not doing a partial match. It is matching exactly what I have entered on sheet 2.

So...it doesnt pull the category from sheet 2 for the keyword telecommunications with telecom on sheet 2 even if I use a wildcard:

=VLOOKUP("*"&A:A&"*",Sheet1!A:B,2,0)

danzon · Feb 15, 2011

maybe if you could give us some real sample data from sheet 2 and then the actual keyword on Sheet 1 that you want to find... give us actual examples of the real data

danzon · Feb 15, 2011

does this work in SHEET 1 cell B2

with your telecommunications example in cell A2

=VLOOKUP(LEFT(A2,4)&"*",Sheet2!A:B,2,0)

MikeMikeMike · Feb 15, 2011

Sheet 1 (Column A -Column B does not exist because that is the one we want to put the vlookup in)

<table border="0" cellpadding="0" cellspacing="0" width="257"><col style="width: 193pt;" width="257"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 193pt;" height="20" width="257">Keyword</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">tele com</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunications</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">tele comm</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">better world telecom</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunication</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunications companies</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecommunications jobs</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">telecom jobs</td> </tr> </tbody></table>

Sheet 2
<table border="0" cellpadding="0" cellspacing="0" width="246"><col style="width: 130pt;" width="173"> <col style="width: 55pt;" width="73"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 130pt;" height="20" width="173">term</td> <td style="width: 55pt;" width="73">category</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>Telecom</td> </tr> </tbody></table>

Really there is nothing more to it. The example phrases should all be in the category Telecom utilizing only those two instances in an ideal world.

danzon · Feb 15, 2011

MikeMikeMike said:
Sheet 1 (Column A -Column B does not exist because that is the one we want to put the vlookup in)

<TABLE border=0 cellSpacing=0 cellPadding=0 width=257><COLGROUP><COL style="WIDTH: 193pt" width=257><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 193pt; HEIGHT: 15pt" height=20 width=257>Keyword</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>tele com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecommunications</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>tele comm</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>better world telecom</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecommunication</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecommunications companies</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecommunications jobs</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>telecom jobs</TD></TR></TBODY></TABLE>

Sheet 2
<TABLE border=0 cellSpacing=0 cellPadding=0 width=246><COLGROUP><COL style="WIDTH: 130pt" width=173><COL style="WIDTH: 55pt" width=73><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 130pt; HEIGHT: 15pt" height=20 width=173>term</TD><TD style="WIDTH: 55pt" width=73>category</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>Telecom</TD></TR></TBODY></TABLE>

Really there is nothing more to it. The example phrases should all be in the category Telecom utilizing only those two instances in an ideal world.

this should do it..

with keywords starting in A2 on Sheet 1

in B2 Sheet 1

=VLOOKUP(LEFT(A2,4)&"*",Sheet2!A:B,2,0)

Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike

New Member

Roseus

Board Regular

Excel Facts

MikeMikeMike

New Member

Aladin Akyurek

MrExcel MVP

MikeMikeMike

New Member

danzon

Well-known Member

MikeMikeMike

New Member

danzon

Well-known Member

danzon

Well-known Member

MikeMikeMike

New Member

danzon

Well-known Member

Similar threads

Forum statistics

Share this page

Partial Text Matching HELP PLEASE! SOS! SOS!!

New Member

Board Regular

Excel Facts

New Member

MrExcel MVP

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

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