First, select AZ7 and define the following...

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Txt:

=LEFT('Update Appraisal'!$AX7,LEN('Update Appraisal'!$AX7))

Array1:

=IF('Update Appraisal'!$BB70,TRANSPOSE(ROW(INDIRECT("1:"&'Upd ate
Appraisal'!$BB7))),1)

Array2:

=ISNUMBER(MATCH(IF(Range=Txt,ROW(Range)+IF('Update
Appraisal'!$BB70,Array1,1),""),IF(Range=IF('Updat e
Appraisal'!$BB70,"",Txt),ROW(Range)),0))+0

Array3:

=MMULT(Array2,IF('Update Appraisal'!$BB70,ROW(INDIRECT("1:"&'Update
Appraisal'!$BB7))^0,1))

Array4:

=ISNA(MATCH(IF(Range=Txt,ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range),""),0 ))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

AZ7, copied down:

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

Hope this helps!

In article <793d2271629ba@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

I see what you mean, a bit confusing. I did have to pause for thought. I
would expect ID 1311.

My qualification or rule of thumb for a penultimate zero (0) EmptyText row
was not made clear. I should have said AT LEAST 2 sequential or consecutive
TEXT rows. In your example, ID 1312 would be the LAST and ID 1311 would be
the penultimate.

Cheers,
Sam

Domenic wrote:

To clarify, if the data contained the following...
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320

...what would you expect as the result?

ID 1311