Bob / Tom,

Thanks for your help.

I have used Bob's array formula to the problem I had:-

=INDEX(Sheet1!$J$1:$J$6,MATCH(1,(Sheet1!$A$1:$A$6= A8)*(MAX(IF((Sheet1!$A$1:$A$6=A8)*(Sheet1!$H$1:$H$ 6<39052),Sheet1!$H$1:$H$6))=Sheet1!$H$1:$H$6),0) )

I have to do some separate workings for this to work as I wanted it to.

For a lot of the MAX values it was looking up I was getting a zero value
which was correct. But if it returned a zero value I wanted it to look up the
preceeding value for the previous MAX value.

In any case:-

Is there any way around the above formula without using an array formula as
this creates massive downtime in my workbook. Is this a setup problem or a
common problem with array formulas?


Due to complexity of the formula etc and the array is this something that
can be done in VB?

Regards
Matthew Balch