VLOOKUP Question on text strings

[boydr]

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.

 

[boydr]

Yogi,

My data looks just like what Aladin entered above. Part of my problem is that there are extreme variances in the way the data (source and array) have been entered.

E.g. I may have IBM Inc. (notice the period) and the other list would not have a period (IBM Inc) and so on. It really varies considerably, and I was trying to extract say the first 3-5 characters and tagging on the "*" to make it find a closer match.

Sounds like it won't quite work the way I had intended. I've spent about 10 hours today going through manually changing punctuations etcc to get it to work right!

Thanks anyway for the help.

 

[Yogi Anand]

Hi boydr:

I may not be understanding what you are trying to accomplish. So, let me try again -- are you saying if your:

lookup value is 'IBM LLC' result should be 'IBM Corporation'
lookup value is 'Jimmy Dean' result should be 'Sara Lee 1'
lookup value is 'State Fair' result should be 'Sara Lee 2'

I f that is the case, look at the following simulation:

y030116h1.xls
	A	B	C	D	E
1	Name1	Name2
2	IBMCorp.	IBMCorporation
3	IBMInc.	IBMCorporation
4	IBMLLC	IBMCorporation
5	JimmyDean	SaraLee1
6	StateFair	SaraLee2
7	TheIBMcorporation	IBMCorporation
8	LookupTable
9
10				lookupvalue	computedresult
11				IBMLLC	IBMCorporation
12				JimmyDean	SaraLee1
13				StateFair	SaraLee2
Sheet11a

</SPAN>

As I said, I may not be understanding what you are trying to accomplish -- if a problem is fairly well defined, there is a good chance we can find a solution.

 

[Aladin Akyurek]

On 2003-01-16 07:46, boydr wrote:
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.

Without morefunc...

Book17
	A	B	C	D	E	F
1
2		Lookupvalues	Result	Hidethiscolumn	Hidethiscolumn
3		IBMLLC	IBMCorporation	IBMCorporation
4		JimmyDean	SaraLee1	SaraLee1
5		StateFair	SaraLee2	SaraLee2
6		IBM	IBMCorporation	IBMCorporation
7		IBMBlue	IBMCorporation	#N/A	IBMCorporation
8
Sheet1