MATCH function - how to find multiple matches ??

K

Keith

I am comparing two worksheets, and the only way I have found to
compare and find both exact and partial matches is by using the Match
function, and since it only works on one column - I need to use it on
one column at a time. The way you can find partial matches is by
appending an asterisk at the beginning and end of the string you are
looking for.

But my database (Sheet1) has 30,000 rows, and has multiple hits for a
given cell in Sheet2. For example, I want to find all customers that
live on Main Street. So in Sheet2, I entered *Main* in cell B4, and
then enter the following in Cell C4. This will search through column
A in sheet1 from Cell A1 through Cell A30000, where the addresses are
stored:

=MATCH(B4,Sheet1!A$1:A$30000,0)

But the problem is that it returns the row number of the first match -
which is 8. However, "Main Street" exists in row 8, row, 37, row 96,
etc.

I want to somehow search for all instances of the word "Main" in
column A. Is there any way to do this?
 
P

Pete McCosh

Keith,

Chip Pearson's page has a good section on different ways
of using the Match function to get more selective results.

http://www.cpearson.com/excel/lookups.htm

Combine this with =Countif(sheet1!A1:A30000,"=*main*") to
get the number of references you need.

Alternatively, a short macro using find and findnext could
do the trick.

Pete.
 
P

Pete McCosh

Keith,

Chip Pearson's page has a good section on different ways
of using the Match function to get more selective results.

http://www.cpearson.com/excel/lookups.htm

Combine this with =Countif(sheet1!A1:A30000,"=*main*") to
get the number of references you need.

Alternatively, a short macro using find and findnext could
do the trick.

Pete.
 
P

Pete McCosh

Keith,

Chip Pearson's page has a good section on different ways
of using the Match function to get more selective results.

http://www.cpearson.com/excel/lookups.htm

Combine this with =Countif(sheet1!A1:A30000,"=*main*") to
get the number of references you need.

Alternatively, a short macro using find and findnext could
do the trick.

Pete.
 
P

Pete McCosh

Keith,

Chip Pearson's page has a good section on different ways
of using the Match function to get more selective results.

http://www.cpearson.com/excel/lookups.htm

Combine this with =Countif(sheet1!A1:A30000,"=*main*") to
get the number of references you need.

Alternatively, a short macro using find and findnext could
do the trick.

Pete.
 
T

Tom Ogilvy

=countif(Sheet1!A$:A$,"*main*")

will give you a count of the number of cells in column A that contain the
characters "main"
 

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