Index & Match

[Dewayne]

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

 

[Max]

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]

Thank you Max. I'll try that. Appreciate your help!
Dewayne

 

[Max]

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