How do I match using partial or approximate values?

G

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

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top