VLOOKUP - Finding matching entries in two columns of text, but in different layouts/format

[mlew1607]

Hello,

Long story short, I have two columns which each contain the same data but in different formats.

I have a column of cells with the addresses of my clients in the following format:
"1 Oak Avenue, AB1 2YZ"

And I have a much larger column of addresses in the following format:
"1 Oak Avenue, Treeville, London, AB1 2YZ"

I have also access to other data, but again the formats differ (Smith, Adam + Mr Adam John Smith respectively).


I need to establish which of my clients fall within the larger list to see if they're affected by a recent development. I have tried Vlookup, using both a True and False at the end. However, the False returns "NA" as my entries do not exactly match the other set and when I use True it returns the first address with the same door no. e.g. 1 Ash Road, even if 1 Oak Avenue is the next entry.

One idea I have, but have not yet tried, is to break down the cell with the full address into separate cells which would be delimited to show only "1 Oak Avenue" and then use Vlookup on these. However, with any minor differences in spelling or abbreviations this may not work and it will probably hit the same barrier as it will look at only door number.

Does anyone have any suggestions of how I can specify more exact criteria for the Vlookup so that it looks for door number and then road name?

Any other suggestions or formula available would be much appreciated.

Thanks

[mrice]

Have a look at

http://www.microsoft.com/en-gb/downl....aspx?id=15011

[mlew1607]

Thank you for the link. Tried at home and appears to work.