array conditional equation

[Craig41Bl]

I'm looking to make a reference to another sheet where if an
employee/date combination is found, the supervisor is pulled from the
other sheet.

Emp Day Super.
Bob Monday ????
Sam Tuesday ????
Mary Wednesday ????

the supervisor column is what i want to fill out, there is another
sheet in the workbook that would have this same information along with
other stuff that I don't need. I was thinking it would be something
along the lines of

{=(If((A2:A5="Bob") And (B2:B5="Monday")), C2:C5)}

but i can't seem to get it working

 

[Biff]

Hi!

Assume this table is on Sheet1 in the range A1:C4-

Emp Day Super.
Bob Monday ????
Sam Tuesday ????
Mary Wednesday ????


You have another table like this on Sheet2 in the range A1:C4-

Emp Day Super.
Bob Monday 1
Sam Tuesday 2
Mary Wednesday 3

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
in sheet1 cell C2:

=INDEX(Sheet2!C$2:C$4,MATCH(1,(Sheet2!A$2:A$4=A2)*(Sheet2!B$2:B$4=B2),0))

Copy down as needed.

Biff

 

[Guest]

Hi,

Try this array formula (Ctrl+shift+Enter)

SUM(IF(($A$12:$A$14=A6)*($B$12:$B$14=B6),$C$12:$C$14))

A12:C14 is

Bob Monday 4
Sam Tuesday 5
Mary Wednesday 6

A6:C8 is as follows

Bob Monday 4
Sam Tuesday 5
Mary Wednesday 6

Hope this helps.

Regards,

Ashish Mathur