Hi Domenic,

Thank you for all your help.

Using the solution below:
1st cell returns #VALUE! error,
2nd cell #REF! error,
3rd cell #REF! error,
4th cell #NA error.

I've tried parts of the solution in separate cells:

Array 3 returns #VALUE! error.

Array3:
=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0 )

Formula returns #VALUE! error.
BA7:
=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)
...confirmed with CONTROL+SHIFT+ENTER

Further help appreciated.

Cheers,
Sam

Domenic wrote:
In that case, try the following instead...

Array1:
=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=ISNUMBER(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(D ata)+Array1,""),IF(Data=
"",ROW(Data)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0 )

Array4:

=ISNA(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data) +Sheet1!$AZ7+1,""),IF(Da
ta="",ROW(Data)),0))

BA7:

=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)

...confirmed with CONTROL+SHIFT+ENTER.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1