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!

In article <78e8c043e0a20@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you very much for reply and assistance.

The first cell returned the LAST ID occurrence of the matched text pattern. I
require the previous / penultimate occurrence of the matched pattern. The
following cells returned error value #N/A.

The Formula:
1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS
($1:1) , so that I can access the correct columns when the Formula is filled
down.

2) I cannot see where in the Formula the varied number of EmptyText rows is
matched. This varies for each cell row and also needs to be filled down. In
my non-working version of the Formula, I was using cell AZ7 to hold the
number of emptyText rows I need to match after the Text value.

3) The Text value and the number of EmptyText rows to match are both variable
criteria.

4) Amending the Formula as in 1 above, all cells return the LAST matched
occurrence, but I require the previous / penultimate occurrence of the
matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7).
These criteria will fill down with each row.

Further assistance appreciated.

Cheers,
Sam

Domenic wrote:
Try...

=IF(AW7<"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T
EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Dat a)-1)="")))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!