How do I match using partial or approximate values?

[gear350z]

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.

 

[T. Valko]

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&"*")

 

[Spiky]

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)

 

[Teethless mama]

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