| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match
I am trying to return the month & year in a cell in which the maximum value
appears with the following formula: =INDEX('Combined Summary'!MaxDate,(MATCH(CombinedMax),(CombinedMax) ,0)) Combined Summary is the sheet name MaxDate (name) is ranges B6:M6 and B33:M33 which includes the months and year i.e Nov-09 thru Oct-10 and Nov-10 thru Oct-11 respectively. CombinedMax (name) is a range of values in B12:M12 and B40:M40 respectively. My formula returns a #NA error. Any help or suggestions would be appreciated. Thank you, Dewayne -- Dewayne |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & Match
Don't think defined names which point to discontiguous ranges (albeit
possible to define), or discontiguous ranges generally, can work in formulas. You could try making it contiguous elsewhere via simple link formulas, then define it for use in formulas. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Dewayne" wrote: I am trying to return the month & year in a cell in which the maximum value appears with the following formula: =INDEX('Combined Summary'!MaxDate,(MATCH(CombinedMax),(CombinedMax) ,0)) Combined Summary is the sheet name MaxDate (name) is ranges B6:M6 and B33:M33 which includes the months and year i.e Nov-09 thru Oct-10 and Nov-10 thru Oct-11 respectively. CombinedMax (name) is a range of values in B12:M12 and B40:M40 respectively. My formula returns a #NA error. Any help or suggestions would be appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & Match
Thank you Max. I'll try that. Appreciate your help!
Dewayne -- Dewayne "Max" wrote: Don't think defined names which point to discontiguous ranges (albeit possible to define), or discontiguous ranges generally, can work in formulas. You could try making it contiguous elsewhere via simple link formulas, then define it for use in formulas. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Dewayne" wrote: I am trying to return the month & year in a cell in which the maximum value appears with the following formula: =INDEX('Combined Summary'!MaxDate,(MATCH(CombinedMax),(CombinedMax) ,0)) Combined Summary is the sheet name MaxDate (name) is ranges B6:M6 and B33:M33 which includes the months and year i.e Nov-09 thru Oct-10 and Nov-10 thru Oct-11 respectively. CombinedMax (name) is a range of values in B12:M12 and B40:M40 respectively. My formula returns a #NA error. Any help or suggestions would be appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & Match
Welcome, pl take a moment to press the YES button (like the one below) in the
response. Thanks. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Dewayne" wrote: Thank you Max. I'll try that. Appreciate your help! Dewayne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| 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 | |||
Linear Mode
