Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
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. -----Original Message----- 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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
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. -----Original Message----- 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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
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. -----Original Message----- 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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
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. -----Original Message----- 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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
Hi Keith
you may try Alan Beban's free array functions (available at http://home.pacbell.net/beban) It includes the function VLOOKUPS which may be what you want. Give it a try :-) -- Regards Frank Kabel Frankfurt, Germany Keith wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH function - how to find multiple matches ??
=countif(Sheet1!A$:A$,"*main*")
will give you a count of the number of cells in column A that contain the characters "main" -- Regards, Tom Ogilvy <Keith wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
how do i find multiple matches of one data item in an excel range | Excel Discussion (Misc queries) | |||
How do I get multiple matches when using the Vlookup function? | Excel Worksheet Functions | |||
Function to find only exact matches in spreadsheets? | Excel Worksheet Functions | |||
Match function selecting first value it matches on exactly | Excel Worksheet Functions |