Let's assume that A1:J5 contains the following table...

51 58 59 65 69 72 73 76 79 Jan-05
50 51 58 72 73 76 79 80 81 Feb-05
50 52 60 62 68 69 70 75 76 Mar-05
53 54 59 60 62 69 70 72 75 Apr-05
50 51 58 59 70 71 72 73 76 May-05

....and that A1:I5 is defined as 'Numbers', and J1:J5 is defined as Date,
the following...

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Number
s)),0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),7
3))))

....can be broken down as follows...

ROW(Numbers)-MIN(ROW(Numbers)) returns the following array of values...

{0;1;2;3;4}

This array of numbers is used as the second argument of the OFFSET
function. Therefore...

OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1) returns the following
array of ranges...

A1:I1
A2:I2
A3:I3
A4:I4
A5:I5

This array of ranges, in turn, is used by both COUNTIF functions...

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72) returns
the following array of values...

{1;1;0;1;1}

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73) returns
the following array of values...

{1;1;0;0;1}

When we combine these two COUNTIF functions...

(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF(O
FFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;0;0;1}

1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF
(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;#DIV/0!;#DIV/0!;1}

MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)
*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)))
returns 5 and is used as an argument for the INDEX function to return
'May-05'.

Note that 1 divided by a number greater than or equal to 0 will always
equal a number less than or equal to 1, except where you divide by 0, in
which case you get #DIV/0!. So when you have...

MATCH(2,1/(COUNTIF(...)*COUNTIF(...)))

....MATCH ignores the #DIV/0! error values in the array of values
returned by 1/(COUNTIF(...)*COUNTIF(...)) and returns the position of
the last numerical value in that array.

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

If you can spare the time, would you mind explaining what each part of the
formula is doing.

Your Formula:
=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

...confirmed with CONTROL+SHIFT+ENTER.

Thanks again for your help.
Sam