Fuzzy Matching an Array against an Array

[duncanfactary]

Hi all,

Have been racking my brains for hours trying to figure this one out but can't get close to achieving what I want...

I have two spreadsheets: one containing 14,000 UK addresses (e.g. Flat 2 14 Kensignton Street London W8 2LY) in Column A, the other containing 250 part addresses (e.g. Kensington Street W8) in Column A.

Essentially I want to identify any of the 14,000 that contains (in the same order but not necessarily as an exact text string) any one of the 250 addresses. If I can also reference them according to the row number in the second spreadsheet, that would be even better.

I guess it requires some kind of MATCH/OR function that includes wild cards (or maybe a VLOOKUP) but I can't figure how to piece together a formula without having to run each of the 250 addresses separately against the 14,000.

Any help would be greatly appreciated.

[amy_d2]

Hi

If row order of both spreadsheets are same according to address ,then you can give them serial numbers at both sheets & mach them with vlookup formula

if its not clear pls share some sample of data

Thanks,
A

[duncanfactary]

This is not possible as the addresses are not the same for each spreadsheet e.g. "Flat 2 14 Kensington Street London W8 2LY" versus "Kensington Street W8". Sorting and assigning serial numbers I don't think will help.

Spreadsheet 1 (14k records) looks like this (the the red text has the numbers removed):

Spreadsheet 1.jpg

Spreadsheet 2 (250 addresses) looks like this:

Spreadsheet 2.jpg

Essentially I want to match the street names in Column A in both spreadsheets.

Cheers

[amy_d2]

Hi
after seeing your data , i would suggest wait for experts to comment on this ,

as per my understanding , there are lot more logics to be put to achieve desired result.

Thanks,
A