Let's assume the following...

A2:A22 contains the ID, and named ID

B2:B22 contains the data, and named Data

AX7 contains the text value to match

AZ7 contains the the number of empty text values to match

A2:B22 contains the following...

1300 1
1301 Empty Text
1302 1
1303 2
1304 Empty Text
1305 1
1306 Empty Text
1307 Empty Text
1308 1
1309 Empty Text
1310 Empty Text
1311 Empty Text
1312 1
1313 Empty Text
1314 Empty Text
1315 1
1316 2
1317 Empty Text
1318 Empty Text
1319 Empty Text
1320 1

AX7 contains 1

AZ7 contains 2

So if we're trying to find the penultimate ID where the text value 1 is
followed by 2 empty text values, the formula should return 1310. If
this is correct, try the following...

Array1:

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

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data=T EXT(Sheet1!$AX7,0),ROW(
Data)+Array1,"")),IF(Data="",ROW(Data)),0))+0)

BA7:

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

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article <7901866312afb@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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