VLOOKUP - "Close Matches"

[John]

I'm running a standard vlookup, with the intention being
that the user will enter a potential new account name into
a field, and the lookup will identify whether that account
already exists. However, due to the similarity between
some of the account names, I want to bring back the
nearest 10 matches as a safety check, not just the one
that the vlookup does at present - can this be done?

All help greatly appreciated....

John

 

[Bernie Deitrick]

John,

Not easily, as Excel doesn't have a built in way of doing it.

You would need to define exactly what a close match was, and write
code to extract those values to show.

HTH,
Bernie
MS Excel MVP

 

[Peo Sjoblom]

Just to add to what Bernie said, you could use a filter like autofilter,
where you can custom filter on begins with, ends with, contains etc..
Of course you can automate that if you add some VBA with for instance an
input box
where you could type the 3 first letters or something like that..

 

[Bernie Deitrick]

But if the user types

XYZZY

and the OP wants values like

YYZZY
TYZZY
XYTZY

filtering won't help, and he's going to have to write some convoluted
code....

HTH,
Bernie
MS Excel MVP

 

[Markus L]

But if the user types
XYZZY
and the OP wants values like
YYZZY
TYZZY
XYTZY
filtering won't help, and he's going to have to write some convoluted
code....

You want to find the 10 values having the smallest "Hamming distance" to the
new entry.
That's quite a complex task. But I'm sure one of the expert will present a
solution here...

Markus

 

[Peo Sjoblom]

True, I would probably use data>validation to make sure the users put in an
allowed name.

 

[Bernie Deitrick]

Peo,

On reflection, the OP would probably be better off using conditional
formatting to indicate if the entry is a duplicate or if it is unique.
That way, the user could enter new values (if so desired) and be made
aware that they entered an incorrect existing value (if that was their
actual intent).

Bernie
MS Excel MVP