array conditional equation

[Craig41Bl@gmail.com]

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

<Craig41Bl@gmail.com> wrote in message
news:1147214256.295265.151830@i40g2000cwc.googlegroups.com...
> 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
>

[Ashish Mathur]

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

"Craig41Bl@gmail.com" wrote:

> 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
>
>