Vlookup Partial Text String

Can you provide a formula that looks for the most similar value of a cell (say A2) in column D of a dataset (say D2:E50) and gives the corresponding value that's in column E? It's exactly like a Vlookup only it's looking up the most similar value instead of the exact value --- the vlookup being vlookup(A2,D2:E50,2,false).

If we need to define "most similar" further, it would either be having the most similar number of letters (this is all text) or at least 7 common letters. The most similar match would be the best...

Thanks a lot for your help in advanced.

D

Can you please attach a dummy workbook?

Sure. I added a very dummed down version of what I am doing. Again, just looking for the closest match and then getting the associated value for that match (column E).

Try,

B3; copy down

Please Login or Register  to view this content.

Interesting, it's working on the dummy sheet but not on the "real" sheet. Does column E have to be numbers? Besides being a lot larger, the only other difference is that column E is text rather than numbers. Any ideas?

I also noticed that I got an NA error when I added " XX" after Allen. It would not longer find the closest match -- that too would present a problem in the live environment.

he only other difference is that column E is text rather than numbers

Try this,

Please Login or Register  to view this content.

I also noticed that I got an NA error when I added " XX" after Allen. It would not longer find the closest match

In that case I think VBA is the good way. You need something like Fuzzy Lookup. I am not a good programmer. Hope some one will help you for the VBA.

Thanks for your help though! I agree that VBA may be the only choice. A fuzzy lookup is exactly what I am trying to achieve -- though I could specifiy wanting to match a certain amount of characters.