Partial Vlookup Match

**OptionTrader** · 11-13-2009, 12:20 PM

In cells A1:A10 I have the following list;

FLOUR
DOUGH
CHICKEN
BEEF
PEPPERS
TOMATOES
CHEESE
ONIONS
POTATOES
SALAMI

I want to be able to get a partial match that will allow for spelling mistakes by the user. The wild card (*) seems to only look at the left-most text and return #N/A if the text isn't matched sequencially.

For example
Cell B1 I type FLO*
C1 has the formula =VLOOKUP(B1,A1:A10,1,FALSE)
The return is FLOUR, great.

But if in B1 I type "FLAUR" (simulating a spelling mistake) it returns #N/A.

Is there any way to get a "best" match ?

p.s. You'll gather my staff can't spell !

**JeanRage** · 11-13-2009, 01:08 PM

Hi,

Why don't you use the Autocomplete feature ... to make your life easier ...

HTH

**OptionTrader** · 11-13-2009, 01:17 PM

Hi Jean

It's complicated, but I can't.

**JeanRage** · 11-13-2009, 01:21 PM

Hi,

Could Data Validation be an alternative ... ???

**OptionTrader** · 11-13-2009, 01:29 PM

Thanks again Jean, but not an option.

Here is the problem in it's entirety;

My staff enter a customers address into a cell e.g, 123 Cavalier Way, which is them matched to a database. The problem is they often get the spelling wrong e.g, 123 Civalier Way which obviously won't then match in the database.

I need to find a way to offer a "similar" match to Civalier Way.

N.B. We are a fast food operation and so time is of the essence.

**mohitspamz** · 11-13-2009, 02:28 PM

Hey!
I was thinking mebbe u could use the zip code to lookup a list in the database but ur into fast food, so i'm guessing zip codes cant work.... could using phone nos. instead be an option?

**OptionTrader** · 11-13-2009, 02:43 PM

Originally Posted by mohitspamz

could using phone nos. instead be an option?

Sure, but only when they are an existing customer i.e, already on the database.

**mohitspamz** · 11-13-2009, 02:46 PM

So wot wud u be looking up for, for new customers using address? :s

**OptionTrader** · 11-13-2009, 03:26 PM

Originally Posted by mohitspamz

So wot wud u be looking up for, for new customers using address? :s

Yes, and if someone could help me with the 1st post problem I could work around the rest of the problem myself.

**DonkeyOte** · 11-13-2009, 03:36 PM

Sorry to be a bore but I'm kind of with the others... in short there is no formula that will do this for you... Data Validation has no Auto Complete feature. You could try and use Fuzzy Vlookup UDFs etc but they will still be at best a "best-guess".

If you want Auto Complete and simultaneously ensure normalised data I would suggest you first have a toggle for Existing/New Customer and when Existing subsequently use a ComboBox control as the basis for Address entry - this will allow you to restrict entries to existing addresses whilst also allowing for Auto Complete so as to speed up the process.

The issue of New Customers and adding data to existing entries is I think for another time.