Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
Right now I'm finding the row of my first match with the following formula.
=MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
Keith,
Here is a different way to do it... http://office.microsoft.com/en-us/ex...260381033.aspx -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Ker_01" wrote in message Right now I'm finding the row of my first match with the following formula. =MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
Thank Jim! I'll give it a try.
"Jim Cone" wrote in message ... Keith, Here is a different way to do it... http://office.microsoft.com/en-us/ex...260381033.aspx -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Ker_01" wrote in message Right now I'm finding the row of my first match with the following formula. =MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
This function will look for a piece of text, in this case 'Ajit', in Col A,
and return all associated values in Col B. =INDEX(B1:B100,MATCH(TRUE,EXACT(A1:A100,"Ajit"),0) ) When you enter the function, hot Ctrl + Shift + Enter, not just enter. Regards, Ryan--- -- RyGuy "Jim Cone" wrote: Keith, Here is a different way to do it... http://office.microsoft.com/en-us/ex...260381033.aspx -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Ker_01" wrote in message Right now I'm finding the row of my first match with the following formula. =MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
Consider using a helper column. Say B11 contains:
Beta and B16 thru.... contains: Alpha Beta Chi Delta Epsilon Beta Gamma Beta Kappa Lambda Mu Name Beta Omega Omicron Phi Pi Psi Beta Sigma Tau Theta Upsilon Xi Zeta Then in C16, enter: =IF($B$11=B16,ROW(),"") and copy on down in an unused cell: =SMALL(C$16:C$40,ROWS($A$1:A1)) and copy down to see: 17 21 23 28 34 -- Gary''s Student - gsnu2007g "Ker_01" wrote: Right now I'm finding the row of my first match with the following formula. =MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding subsequent matches
Ker_01 wrote:
Right now I'm finding the row of my first match with the following formula. =MATCH($B$11,$B$16:$B$10024,FALSE)+15 However, with new data coming in, there may be more than one match that I need to know about. I'd like to use multiple cells to capture the row number of each subsequent match. My current approach is something like [A1] =MATCH($B$11,$B$16:$B$10024,FALSE)+15 [A2] =indirect("MATCH($B$11,$B$" & A1 & ":$B$10024,FALSE)+15") [A3] =indirect("MATCH($B$11,$B$" & A2 & ":$B$10024,FALSE)+15") Is there a more elegant way to find subsequent matches, rather than just doing a new match based on resetting the start of the match range? Thanks, Keith If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, array enter into a column of cells sufficiently long to accommodate all matches =INDEX(ArrayMatch(B11,B16:B1024),0,1)+15 Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding matches ( | Excel Discussion (Misc queries) | |||
Finding matches (in Excel 2007) | Excel Discussion (Misc queries) | |||
finding exact matches | Excel Worksheet Functions | |||
Finding EXACT matches within separate rows | Excel Discussion (Misc queries) | |||
finding exact matches using vlookup | Excel Worksheet Functions |