Should this be a lookup or an array formula?

[Sam Longstaff]

Hi,

I want to be able to list the staff members name against the date where the words "ON CALL" are entered in the cell. I have attached an example workbook for you to see what I mean.

Thanks for any help.

 

[bosco_yip]

Try,

In BD10, formula copied down :

=IFERROR(INDEX($A$10:$A$45,MATCH(BD$9,INDEX(V$10:AZ$45,,MATCH(BC10,V$8:AZ$8,0)),0)),"")

Regards
Bosco

 

[Sam Longstaff]

Fantastic. Thanks Bosco!

 

[rahulshewale1]

hii @Sam Longstaff ,

this one also works.try

Cell BE 10 :

=INDEX($B$10:$B$45,MATCH(BE$9,INDEX($W$10:$BA$45,,MATCH(BD10,$W$8:$BA$8,0)),0))


Regard
Rahul shewale

 

[rahulshewale1]

sorry ,

i pasted bosco sir formula,


=INDEX($B$10:$B$45,MATCH(BE$9,OFFSET($W$10,,MATCH(BD10,$W$8:$BA$8,0)-1,50,1),0))



Regard
Rahul shewale