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!

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