Formula help please :-)

[SHARPY1]

Hi all,

I have data columns G-I
as below

sensitive data removed

I would like a formula in column J that displays column I at next occurrence of sensitive data removed
so for example in cell J1 would = sensitive data removed
J2,J3,J4 would be the same also
J5 would = sensitive data removed
and so on...

Any help greatly appreciated
Cheers

 

[GlennUK]

Try this array formula ( entered using Ctrl-Shift-Enter instead of Enter ):

=INDEX($I$1:$I$28,MIN(IF(G1:$G$28="...",ROW(G1:$G$28),9999)))

 

[SHARPY1]

sensitive data removed

Works for so long, but then in cell J131 at top of screen displays
=INDEX($I$131:$I$5000,MIN(IF(G131:$G$5000="...",ROW(G131:$G$5000),9999)))

sensitive data removed

should be sensitive data removed

Any ideas?
Cheers

 

[GlennUK]

Yeah, you are pointing a range that starts in row 131 for the INDEX to fetch from, whereas I'm returning the row number. That's why my formula points at 1 here INDEX($I$1

 

[SHARPY1]

Cheers matey