Hi Domenic,

Thanks for further assistance.

Using your example below, my expected and required result would be ID 1307.
The number of EmptyText cell rows must be an explicit match. Thereby, a match,
if the number of EmptyText rows is an exact sequential match. If looking for
2 EmptyText cell rows then, there should be exactly 2 EmptyText rows below
the Text for it to be a match.

Cheers,
Sam

Domenic wrote:
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...

No, using your example above, my expected and required result would be ID
1307.
The number of EmptyText cell rows must be an explicit match. Thereby, a match,
if the number of EmptyText rows is an exact sequential match.

Array1:

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

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data= TEXT(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!

--
Message posted via http://www.officekb.com