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 15, 2011

danzon said:
does this work in SHEET 1 cell B2

with your telecommunications example in cell A2

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

I get a circular reference warning and then a 0 is displayed.

danzon · Feb 15, 2011

MikeMikeMike said:
I get a circular reference warning and then a 0 is displayed.

not possible... that would mean you were putting the formula in the same sheet that you are looking up...

make sure you are putting it in SHEET 1 B2 and the formula is referencing SHEET 2

this works for me using your sample data exactly as you posted it

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

Aladin Akyurek · 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.

Try also post #13...

danzon · Feb 15, 2011

To post this sample I put the lookup table in the same sheet but my formula works fine

for your layout on separate sheets use

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

Haseeb Avarakkan · Feb 15, 2011

Try on Sheet1!B2, copy down...

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

Aladin Akyurek · Feb 15, 2011

Haseeb Avarakkan said:
Try on Sheet1!B2, copy down...

=LOOKUP(2,1/SEARCH(Sheet2!$A$2:$A$4,A2),Sheet2!$B$2:$B$4)

This is more expensive than the formula (similar kind) already posted (see post #13)...

Haseeb Avarakkan · Feb 15, 2011

Aladin Akyurek said:
This is more expensive than the formula (similar kind) already posted (see post #13)...

Yes, yours is much faster than mine. Thank you Aladin.

MikeMikeMike · Feb 15, 2011

danzon said:
To post this sample I put the lookup table in the same sheet but my formula works fine

for your layout on separate sheets use

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

Wow the responses! I this only checking the 4th character in a2? If so then this will not work as (for example) A12 contains 'jobs telecommunications'.

MikeMikeMike · Feb 15, 2011

Thank you all...however none have worked.

The closest unless someone wants to try and correct me is this:

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

But it searching for the 4th character defeats the purpose of this exercise...I literally want a partial text match.

Counting exact matches to the fourth character to the left is a dirty work around that is not scalable, in regards to this exercise that is. It will not work because many instances telecommunications (and its variations) exist however they are often times led by another word making the 'e' in tele not the 4th character from the left like this (the closest I was able to get to work and/or made sense) does not work.

But seriously...thank you for trying.

Anyone else want to take a stab at this - or at least give me good solid advice on what needs to happen (in regards to fuzzylookups) or point me to a board where I can repost my question I would greatlyt appreciate it.

MikeMikeMike · Feb 16, 2011

It would be awesome if this (or something like it) worked:

=VLOOKUP("*"&(A4)&"*",E:F,2,0)

Any suggestions?

Partial Text Matching HELP PLEASE! SOS! SOS!!

MikeMikeMike

New Member

MikeMikeMike

New Member

danzon

Well-known Member

Aladin Akyurek

MrExcel MVP

danzon

Well-known Member

Haseeb Avarakkan

Well-known Member

Aladin Akyurek

MrExcel MVP

Haseeb Avarakkan

Well-known Member

MikeMikeMike

New Member

MikeMikeMike

New Member

MikeMikeMike

New Member

Similar threads

Share this page

Partial Text Matching HELP PLEASE! SOS! SOS!!

New Member

New Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

New Member

New Member

New 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