| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index/Match maybe???
Excel2003 ...
Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Try this:
Enter this formula in Q3 as an array**: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Copy across to a number of cells that will equal the maximum number of ties in any of the rows. For example, if there are a max of 3 ties in any single row then you need to copy the formula across to at least 3 columns. Then copy down as needed. ** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
T. ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask Trevor same thing ... His formula works fine too for what it does. Thank you for the guidance ... Kha "T. Valko" wrote: Try this: Enter this formula in Q3 as an array**: =IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"") Copy across to a number of cells that will equal the maximum number of ties in any of the rows. For example, if there are a max of 3 ties in any single row then you need to copy the formula across to at least 3 columns. Then copy down as needed. ** entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
One way:
=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a
moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
OK
MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Trevor ... (Happy morning)
I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match maybe???
Thanks for the feedback. You have a great day too
"Ken" wrote in message ... Trevor ... (Happy morning) I placed values in the applicable Range of a Blank WorkBook & worked this formula thru to help me better understand the Formulas & sequence of calculations (excellent explanation) ... I have learned much from those that are intimate with Excel & support these boards ... Please know I appreciate the time you have taken to provide this guidance for one of my many Excel short-comings. My Thanks ... Have a Happy & Safe Day ... Kha "Trevor Shuttleworth" wrote: OK MAX(B3:P3) returns the maximum value in the range B3 to P3 MATCH(MAX(B3:P3),B3:P3,0) returns the offset of the maximum value within the range MATCH(MAX(B3:P3),B3:P3,0)+1 calculates the column number (taking into account we start in column B) ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) turns that into an address ... row 2, column whatever we just calculated =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) gets the value from the calculated address So, let's say the maximum value of 9 was in cell L3 which is column 12 =MAX(B3:P3) = 9 =MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11 =MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12 =ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2 =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) = column 12 or whatever Regards Trevor "Ken" wrote in message ... Trevor ... Formula works flawlessly (Thanks) ... That said ... Can you take a moment to give me a step by step (in order) line-item breakdown of how this formula works? I need to ask T. Valko same thing ... His formula is flawless too. Thank you for the guidance ... Kha "Trevor Shuttleworth" wrote: One way: =INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) If there are multiple maximum values, this will return the name of the first one. Regards Trevor "Ken" wrote in message ... Excel2003 ... Range B2:P2 ... Names Range B3:P42 ... Values Looking for Formula in Range Q3:Q42 to return: Q3 ... "Name" associated with MAX Value found in Range B3:P3 Q4 ... "Name" associated with MAX Value found in Range B4:P4 Q5 ... "Name" associated with MAX Value found in Range B5:P5 Note: also need to know how to handle if multiple "Names" found with same MAX Value in the Range??? Thanks ... Kha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
| INDEX / MATCH | Excel Worksheet Functions | |||
| Index/Match Help | Excel Worksheet Functions | |||
Hybrid Mode
