Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array conditional equation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array conditional equation
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 wrote in message oups.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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array conditional equation
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equation to refer to data value(s) and not the cell?? | Excel Discussion (Misc queries) | |||
Problem with Vlookup array selection | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
Return Array with Array | Excel Worksheet Functions |