I want to compare/match data from two 1,000+ row spreadsheets where the
values are similar but not exactly alike. For example, on one spreadsheet I
have a name like John P. Smith Associates, and on the other spreadsheet I
have John Smith Assoc. I would like to find and return a match using a
partial value, such as "Smith Assoc" ...sort of using a wildcard containig
the value. I've tried using vlookup for exact and approx matches but it did
not work out for me.

What kind of result are you looking for?

=COUNTIF(Sheet2!A1:A1000,"*Smith Assoc*")

Or, using a cell reference:

A1 = Smith Assoc

=COUNTIF(Sheet2!A1:A1000,"*"&A1&"*")

--
Biff
Microsoft Excel MVP


"gear350z" wrote in message
...
I want to compare/match data from two 1,000+ row spreadsheets where the
values are similar but not exactly alike. For example, on one spreadsheet
I
have a name like John P. Smith Associates, and on the other spreadsheet I
have John Smith Assoc. I would like to find and return a match using a
partial value, such as "Smith Assoc" ...sort of using a wildcard
containig
the value. I've tried using vlookup for exact and approx matches but it
did
not work out for me.

Or some others....

Note this is an exact reference, use FALSE. This will return the value
(text) in the first cell it finds with "smith":
=VLOOKUP("*smith*",A1:A100,1,FALSE)

Try MATCH instead if you are looking for a reference. This will return
the row number of that cell:
=MATCH("*smith*",A1:A100,0)

=VLOOKUP("*Smith Assoc*",A:B,2,0)


"gear350z" wrote:

I want to compare/match data from two 1,000+ row spreadsheets where the
values are similar but not exactly alike. For example, on one spreadsheet I
have a name like John P. Smith Associates, and on the other spreadsheet I
have John Smith Assoc. I would like to find and return a match using a
partial value, such as "Smith Assoc" ...sort of using a wildcard containig
the value. I've tried using vlookup for exact and approx matches but it did
not work out for me.