Comparing two lists in excel that don't match exactly

[Guest]

I'm running Excel 2003. I'm trying to compare two lists that don't match
exactly. I've been using Vlookup, but if the cell content of my search is
not the same as the array I'm comparing it to, it does not find it.
For example: I'm searching for "Mary Smith". The array contains "Smith,
Mary". VLookup won't find the record because it doesn't match exactly what
I'm searching for. Because I'm comparing thousands of records against an
extremely large database, it's not feasable for me to just do a search.
Any insight?

Thanks,

 

[Guest]

Why don't you try parsing "Mary Smith" into "Mary" and "Smith" in adjacent
columns and then concatenate them again

A1 = Mary Smith
B1 = Mary
C1 = Smith
D1 = C1 & ", " & B1

When you are done, try the lookup again.

 

[Guest]

Barb,

Thanks for the quick reply. Unfortunately, the database I'm searching
against doesn't always have a comma separating the names. Sometimes there's
a space, sometimes the names are inverted. There are also company names
listed with "Corp.", "Corp", "Corporation", etc. . I guess what I'm really
looking for is for the search to bring back any instance of "Mary" or
"Smith", regardless of how it's listed in the database record. Not sure if
that's possible though.