Hi Domenic,

Thank you for your time and assistance.

1) The Formula returns incorrect results with some #REF! errors. I think the
#REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure?

2) Also, I think the Formula is trying to return the penultimate ID that
matches the specific TEXT criteria above the EmptyText rows. I require the
penultimate ID that matches first, Text criteria and then the specific number
of variable EmptyText rows below the Text criteria. The penultimate ID
returned should match with the last EmptyText row criteria.

Further assistance most appreciated.

Cheers,
Sam

Domenic wrote:
Assuming that the formula will be entered in BA7 and copied down, try
the following...

1) Select cell BA7

2) Define the following...

Insert Name Define

Name: Range

Refers to:

=INDEX(Data,0,ROWS($BA$7:$BA7))

Click Add

Name: Arrray1

Refers to:

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

Click Add

Name: Array2

Refers to:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range =TEXT($AX7,0),ROW(Range)
+Array1,"")),IF(Range="",ROW(Range)),0))+0)

Click Ok

3) Enter the following formula in BA7, and copy down:

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

...confirmed with CONTROL+SHIFT+ENTER

**Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be
replaced with $AX7.

Hope this helps!

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