Hi Domenic,

I have not tried the solution below as yet. We may be at cross-purposes.

The only problem with the existing solution is, if I wish to locate an
EmptyText row, that is; when the match of EmptyText is 0: there should be two
sequential row matches of the same TEXT.

For example, to find the ID of the penultimate EmptyText row of zero (0) for
TEXT "1" should return ID 1303.

Sample Data Layout:
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306
1307
1308 1
1309
1310 1
1311
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320 1

I am now getting most of the expected ID results. However, I still have a
problem returning the correct ID result where zero "0" EmptyText rows needs
to be matched.

Cheers,
Sam


Domenic wrote:
With the layout as you described in your previous post, try the
following...

1) Select 'Update Appraisal'!AZ7 and define the following:

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307, 'Site
Lond'!$A$12:$A$65536)),,,,51)

Range:

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

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:

=ISNUMBER(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+Array1,""),IF(Range= "",ROW(Range)),0))+0

Array3:

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

Array4:

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

2) Then enter the following formula in 'Update Appraisal'!AZ7 and copy
down:

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

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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