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


+-------------------------------------------------------------------+
|Filename: Array.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4531 |
+-------------------------------------------------------------------+

 

[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]...

 

[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
different sheets, results came out different. (Although the formul
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

+-------------------------------------------------------------------
|Filename: Array2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4535
+-------------------------------------------------------------------