Nested if in Array

[TheLeafs]

Good day,

I have attached my speadsheet to simpfly my describtion of my troubles. On the Team1 tab I have a formula entered in cell C4 that should work but it doesn't. What I am trying to do is lookup the Employee's ID and Day of the Week on the Time Off tab, and if it finds both for that pictular day of the week then return and "A" for absent and null string if it finds nothing. My current formula is working for the first cell but it isn't for the ones below.
Any help would be great.

Chris

[Ardus Petus]

Can a given empid appear several times on different rows of sheet Time_Off?

Regards,
--
AP

"TheLeafs" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Good day,
>
> I have attached my speadsheet to simpfly my describtion of my troubles.
> On the Team1 tab I have a formula entered in cell C4 that should work
> but it doesn't. What I am trying to do is lookup the Employee's ID and
> Day of the Week on the Time Off tab, and if it finds both for that
> pictular day of the week then return and "A" for absent and null string
> if it finds nothing. My current formula is working for the first cell
> but it isn't for the ones below.
> Any help would be great.
>
> Chris
>
>
> +-------------------------------------------------------------------+
> |Filename: Array.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4531 |
> +-------------------------------------------------------------------+
>
> --
> TheLeafs
> ------------------------------------------------------------------------
> TheLeafs's Profile:
http://www.excelforum.com/member.php...o&userid=10131
> View this thread: http://www.excelforum.com/showthread...hreadid=526784
>

[TheLeafs]

Hey Ardus,

EmpID can appear more then once, but not of the same day of the week.

Thanks

[Morrigan]

Something seems odd, I literally tried the exact same formula on 2 different sheets, results came out different. (Although the formula doesn't make much sense without array input) See attachment.

Try this instead

C4 = IF(SUMPRODUCT(--(Time_Off!$A$2:$A$1152=Team1!$A4),--(Time_Off!$F$2:$F$1152=Team1!C$1))>0,"A","")

Copy across and down.