Can Vlookups cope with spelling errors?

I have a number of Vlooks set up to reference cost information set up against Names in various workbooks.

The problem is wherever a name is mis-spelt then the lookup fails to match the values.


Is there anyway around this, or is it just tough luck!?

There might be. Try googling "fuzzy look-up".

I've had a look but can't find instructions on how to use this. I am guessing that this isnt a standard function, but is something I would need to download?

I don't know what you're referring to, but there is no built-in function.

If you want any meaningful help, the least you can do is attach a sample of what you're working with, otherwise everyone is working blind.

OK - sorry,

Attached is an example of typical issues I am finding. The difference in spellings stop the lookup working but I can't find an explanation of how to approach this with 'fuzzylookup' without going into Visual Basic which I dont know how to use..yet.


Thanks

It looks quite involved, and in theory it's probably bottomless in terms of how deeply one can delve into it. I think the amount of effort depends on how many unmatched names you are having to deal with. If it's <100, do it manually. If you're matching the same datasets repeatedly, start a lookup list so you can match up the error entries. If it's 100s or 1000s, it needs more serious work so someone here may be able to help. EDIT: a further thought is that if they're always two names, you could split them into two columns and start matching first names/surnames to whittle down possibilities a bit more.

Originally Posted by StephenR

It looks quite involved, and in theory it's probably bottomless in terms of how deeply one can delve into it. I think the amount of effort depends on how many unmatched names you are having to deal with. If it's <100, do it manually. If you're matching the same datasets repeatedly, start a lookup list so you can match up the error entries. If it's 100s or 1000s, it needs more serious work so someone here may be able to help. EDIT: a further thought is that if they're always two names, you could split them into two columns and start matching first names/surnames to whittle down possibilities a bit more.

Thanks - its upto 500 names....I think splitting them into two columns might be the way to go. Also a look up list. And also a strong email to people to use correct spelling on sheets!

Thanks

Or be Stalinist and impose a pre-prepared list on everyone.

Or make poor spelling a sackable offence.

Come back if you need further help (in relation to your reply, not the above).

Hi - thanks for looking at this futher.

How are the names in column C arranged, is it random, does it matter?

shg may drop by. I don't think his very clever code needs the names to be arranged in any particular way. Try changing it and see what happens.

Thanks for your help

Originally Posted by shg

They're just in alphabetical order, but it doesn't matter.

shg: excellent solution, though i'm wondering if you are using a larger list is there a more efficient way? i've got 1 column with over 10,000 entries and it just kills excel :/