Search beginning from last match

=MATCH(1, OFFSET(A4:A80, MATCH(1, A4:A80, 0), 0):OFFSET(A4:A80, 76, 0), 0)

Messy, but non-volatile.

Hi, thank you for your time.

In my struggles with this, I have actually come up with this function....

=OFFSET(INDIRECT(ADDRESS(MATCH(W2,OFFSET(INDIRECT(ADDRESS(MATCH(W1,R1:R$2001,0),1,4,"TRUE"),1),1,0,1,1):A2001,0),1,4,"TRUE"),1),-1,2,1,1)

Woah! This is one of the longest functions I have constructed in Excel thus far (yipee!) Unfortunately, it doesn't work. I get a N/A value returned. Why is this?

What I am trying to do is take data (W2) which is matched up with information in another column (R1:R2001) -this data is actually contrived from a formula which pulls from column A - get the address (from W1 match) and have it offset from that address to find the next match in (W2).... and then offset from that address to pull the desired information....

I have this formula, which works great

=OFFSET(INDIRECT(ADDRESS(MATCH(W1,R1:R$2001,0),1,4,"TRUE"),1),-1,1,1,1)

The trick is starting another search from the address value of the formula above...

Make sense?... I hope... Thanks...

And you tried the formula I provided, but ... what?