VLOOKUP Question on text strings

boydr

Board Regular
Joined
Apr 17, 2002
Messages
157
I have a worksheet that has a column of names in it. I'm trying to VLOOKUP to another sheet, column A, and returning the value in Column B if it finds the lookup.

All columns are text columns with names.

When I use the 'true' option in the vlookup, the results are about 50% accurate, and if I use 'false'then it's only about 5% accurate. The problem is that the spelling varies between the two columns. Is there an accurate way to do this?

Say on sheet1 in column B I have the vlookup:

=(C1,'sheet'!A1:B800,2,false) or
=(C1,'sheet'!A1:B800,2,true)

Can you vlookup with a partial string? I have about 17000 lines that I need to populate with this.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi boydr:

In reference to your question regarding use of partial strings in using VLOOKUP function, YES! you can -- see the following simulation:
y030115h1.xls
ABCDEF
1
2MrExcel100200oyd201
3boydr101201Iusedapartialstringfromboydr entrytolookupthevalueinthe thirdcolumnofthelookuptable
4PaddyD102202
5JoeWas103203
6Maxflia10104204
Sheet7
</SPAN>

Please post back if it works for you -- otherwise explain a little further and let us take it from there.
 
Upvote 0
Yogi,

Thank you for responding. I think that is what I need, but can you explain what the 0 represents at the end of the command? I'm only familiar with true or false.
 
Upvote 0
On 2003-01-15 23:31, boydr wrote:
Yogi,

Thank you for responding. I think that is what I need, but can you explain what the 0 represents at the end of the command? I'm only familiar with true or false.

Hi boydr:

We are talking about the same thing -- You can use 1 for TRUE ; 0 for FALSE
 
Upvote 0
And,

Your vlookup worked on part of it.

Can you tell me why this might not work?
==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

On sheet2:
Vlookup array table looks simplar to:
Column A Column B
IBM Corp. IBM Corporation
IBM Inc. IBM Corporation
IBM LLC IBM Corporation
Jimmy Dean Sara Lee
State Fair Sara Lee
ETC...

Source in Column B sheet1
IBM LLC
Jimmy Dean
State Fair
Etc.
Formula is in Column A sheet1
(I want it to pull from vlookup array)
 
Upvote 0
And,

Your vlookup worked on part of it.

Can you tell me why this might not work?
==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

Hi boydr:

1. What is the complete entry that makesup the LOOKUP value?
2. What string are you using with what wild cards in the VLOOKUP formula?
3. What is your range for the LOOKUP table?

If you can supply that we can look at where the problem might be -- I don't think the LookupTable being in another sheet is the issue.
 
Upvote 0
On 2003-01-15 23:49, boydr wrote:
And,

Your vlookup worked on part of it.

Can you tell me why this might not work?
==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

On sheet2:
Vlookup array table looks simplar to:
Column A Column B
IBM Corp. IBM Corporation
IBM Inc. IBM Corporation
IBM LLC IBM Corporation
Jimmy Dean Sara Lee
State Fair Sara Lee
ETC...

Source in Column B sheet1
IBM LLC
Jimmy Dean
State Fair
Etc.
Formula is in Column A sheet1
(I want it to pull from vlookup array)

Although it is possible to do partial matching using VLOOKUP, it will not be a full-fledged "fuzzy match"...

By the way, if it's possible for you to use the morefunc.xll, you can make the retrieval task a lot faster.

Consider the following lookup table...
aaPartialMatchVlookup boydr.xls
ABCD
4
5IBMCorp.IBMCorporation
6IBMInc.IBMCorporation
7IBMLLCIBMCorporation
8JimmyDeanSaraLee1
9StateFairSaraLee2
10TheIBMcorporationIBMCorporation
11
12
Sheet2


What follows shows how you can set up VLOOKUP for partial matching...
aaPartialMatchVlookup boydr.xls
ABCD
2
3LookupvaluesResult
4IBMLLCIBMIBMCorporation
5JimmyDeanJimmySaraLee1
6StateFairStateSaraLee2
7IBMIBMIBMCorporation
8
9
10
Sheet1
 
Upvote 0
On 2003-01-16 00:08, Yogi Anand wrote:
And,

Your vlookup worked on part of it.

Can you tell me why this might not work?
==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

Hi boydr:

1. What is the complete entry that makesup the LOOKUP value?
2. What string are you using with what wild cards in the VLOOKUP formula?
3. What is your range for the LOOKUP table?

If you can supply that we can look at where the problem might be -- I don't think the LookupTable being in another sheet is the issue.


Yogi,
1)The lookup range varies (have 30 sheets to run this on) but the first range is on sheet1
B2:B838
2) ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
3) Lookup range is sheet2:A2:B1288
 
Upvote 0
Aladin,

Thanks for the response, but I can't use that addin (unfortunately.) I'm doing this at work and can't install programs. I think you may be right in that I'm using fuzzy logic, and it's very difficult to get the rate match in my situation.
 
Upvote 0
Yogi,
1)The lookup range varies (have 30 sheets to run this on) but the first range is on sheet1
B2:B838
2) ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
3) Lookup range is sheet2:A2:B1288

Hi boydr:

1. Can you post what is housed in cell B4 in your VLOOKUP formula (that should be on sheet1 --right?)
2. Can you post what is indeed the looked up value (which comprises the string in cell B4 of sheet1)
3. Can you post the contents of the Lookup table A5:B6 -- these should be from sheet2 -- right?

This way we can determine what you are looking at and analyze what is breaking down.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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