How to list multiple occurances ACROSS A ROW

[Sam Longstaff]

Hi,

I have seen the guide on how to list multiple occurrences from a column but I am unable to transfer the same principle when looking across rows. How would I do this please?

 

[Chihiro]

Sample file please and manually created expected output. Not sure what you are trying to accomplish here.

 

[Sam Longstaff]

hi its for interrogating a rota to get the dates that a staff member would be working. See attached file.

 

[Chihiro]

In BB14:
=INDEX($V$8:$AZ$8,SMALL(IF(INDEX($V$10:$AZ$45,MATCH($BB$12,$A$10:$A$45,0),)=BB$13,COLUMN($V$8:$AZ$8)-COLUMN($V$8)+1),ROW(A1)))

Confirmed as array (CSE). Copy across and down.
If you don't want #Num! error, nest it in =IFERROR(formula,"")

 

[Khalid NGO]

Hi Sam,

Try this {array formula} in BB14:

=IFERROR(INDEX($V$8:$AZ$8,,SMALL(IF(($V$10:$AZ$45=BB$13)*($A$10:$A$45=$BB$12),COLUMN($V$1:$AZ$1)-21),ROW(A1))),"")

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Regards,

Ops: didn't noticed Chihiro replied a few minutes ago

 

[Sam Longstaff]

Thanks very much folks, that has worked a treat. Appreciate your fast replies.