Row/Index Function

[mulholm]

=IF(ROWS($A$28:A28)>COUNTIF(Utilisation!C62:C68,"Paid Breaks"),"",INDEX(Utilisation!A62:A68,SMALL(IF(Utilisation!C62:C68="Paid Breaks",ROW(Utilisation!C62:C68)-ROW(Utilisation!C62)+2),ROWS($A$28:A28))))

Is there any way to amend this formula so that it searches all of column "C" for the word "Paid Breaks" and returns the corresponding namein column "A".

I have managed to get it to work if the rows in column "C" were a constant, i.e "C62:C68" as per above forumla however the rows will change depending on how many people i have in that day so is not a constant.

Therefore i need it for the whole column rather than specific rows.

 

[mulholm]

PS it your formula to work if with C62 + A62

Currently it is C62:C68 but tomorrow it could be C69:C80 or C1:C16 so it goes up and down and isnt always a set number between ranges.

 

[Aladin Akyurek]

PS it your formula to work if with C62 + A62

Currently it is C62:C68 but tomorrow it could be C69:C80 or C1:C16 so it goes up and down and isnt always a set number between ranges.

Is there any other information which could be used to determine where the C starts end where it ends?

 

[mulholm]

Unfortunately not , this is where the problem comes from.

 

[Aladin Akyurek]

Unfortunately not , this is where the problem comes from.

Let's try the expesive version. Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Utilisation!$A:$A,SMALL(IF(Utilisation!$C:$C="Paid Breaks",ROW(Utilisation!$A:$A)),ROWS($1:1))),"")

 

[mulholm]

Worked a treat

3 weeks I've been trying to fix this.

Legend.

 

[Aladin Akyurek]

You are welcome. Thanks much for providing feedback.