Multiple IF factors

I have a spreadsheet with
A - Day of week
B - Time of Day
C - etc - Employee names
Day Time Amy P Angel M Chad S Deborah J
Monday 6:00 AM AUX
Monday 6:15 AM AUX
Monday 6:30 AM AUX
Monday 6:45 AM AUX
Monday 7:00 AM AUX
Monday 7:15 AM AUX
Monday 7:30 AM Phone AUX
Monday 7:45 AM Phone AUX
Monday 8:00 AM Phone Break
Monday 8:15 AM Phone AUX
Monday 8:30 AM Aux Phone AUX
Monday 8:45 AM AUX Phone AUX
Monday 9:00 AM AUX Phone AUX
Monday 9:15 AM AUX Phone Phone
Monday 9:30 AM AUX Break Phone
Monday 9:45 AM AUX Phone Phone


Rows indicate what job function they are doing each 15 minutes throughout
the day.

I need to create a formula that will tell me which employees are doing a
particular job function (break, lunch, etc), on a specific day (Tues) for
a certain time (9:15 am)
I have a separate worksheet started with
Day of week, job function and time and list each employee under it,
Monday
Phones
10:00 AM

Amy P ?????
Angel M ?????
Chad S ?????
Deborah J ???
Can anyone help me?

Hi!

This solution is based on your sample data as posted.

Assume your table is on Sheet1 A1:F17.

On Sheet2 you have in:

A1 = Monday
A2 = Phone
A3 = 9:45 AM
A4 = (blank)

In A5 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1!A$2:A$17=A$1)*
(Sheet1!B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN
(A:D)),ROW(1:1)))

Copy down until you get #NUM! errors.

This formula will return:

A5 = Angel M
A6 = Deborah J
A7 = #NUM!

You can suppress the #NUM! errors by using an error trap
in the formula but that will make the formula twice as
long:

=IF(ISERROR(SMALL(IF((Sheet1!A$2:A$17=A$1)*(Sheet1!
B$2:B$17=A$3)*(Sheet1!C$2:F$17=A$2),COLUMN(A:D)),ROW
(1:1))),"",INDEX(Sheet1!C$1:F$1,SMALL(IF((Sheet1!
A$2:A$17=A$1)*(Sheet1!B$2:B$17=A$3)*(Sheet1!
C$2:F$17=A$2),COLUMN(A:D)),ROW(1:1))))

An alternative is to use the shorter formula and then use
conditional formatting to hide the errors. Example:

If you would normally expect to have 5 employees that meet
the criteria of Monday, Phone, 9:45 AM, then you would
want to copy the formula to AT LEAST 5 cells, So:

Select the range A5:A9
Goto Format>Conditional Formatting
Formula is: =ISERROR(A5)
Click the Format button.
Set the font color to be the same as the fill color.
OK out.

You could also make the formula much shorter by using
defined named ranges.

Biff

>-----Original Message-----
>I have a spreadsheet with
>A - Day of week
>B - Time of Day
>C - etc - Employee names
>Day Time Amy P Angel M Chad S Deborah J
>Monday 6:00 AM AUX
>Monday 6:15 AM AUX
>Monday 6:30 AM AUX
>Monday 6:45 AM AUX
>Monday 7:00 AM AUX
>Monday 7:15 AM AUX
>Monday 7:30 AM Phone AUX
>Monday 7:45 AM Phone AUX
>Monday 8:00 AM Phone Break
>Monday 8:15 AM Phone AUX
>Monday 8:30 AM Aux Phone AUX
>Monday 8:45 AM AUX Phone AUX
>Monday 9:00 AM AUX Phone AUX
>Monday 9:15 AM AUX Phone Phone
>Monday 9:30 AM AUX Break Phone
>Monday 9:45 AM AUX Phone Phone
>
>
>Rows indicate what job function they are doing each 15
minutes throughout
>the day.
>
>I need to create a formula that will tell me which
employees are doing a
>particular job function (break, lunch, etc), on a
specific day (Tues) for
>a certain time (9:15 am)
>I have a separate worksheet started with
>Day of week, job function and time and list each employee
under it,
> Monday
> Phones
> 10:00 AM
>
>Amy P ?????
>Angel M ?????
>Chad S ?????
>Deborah J ???
>Can anyone help me?
>
>
>.
>

Thanks Biff, but I still have something wrong as I can't get the data to
fill is after 10:00 am. I have 286 rows of info and Columns thru "S"
The formula I used was:
=IF(ISERROR(SMALL(IF((Schedule!A$2:A$286=A$1)*(Schedule!B$2:B$286=A$3)*(Schedule!C$2:S$286=A$2),Column(A:Q)),ROW(1:1))),"",INDEX(Schedule!C$1:S$1,SMALL(IF((Schedule!A$2:A$286=A$1)*(Schedule!B$2:B$286=A43)*(Schedule!C$2:S$286=A$2),Column(A:Q))),Row(1:1))))

Can you see what I've done wrong?
Thanks for your help! I do appreciate it very much.
Kamille

Hi!

The formula looks OK.

I created a test file the size that you mentioned:

286 rows by 19 columns.

Times start at 6:00 AM to 8:00 PM in 15 min increments for
Monday through Friday.

Randomly filled the table with "phone", "aux", and "break".

It works for my test file. No problems!

There's nothing wrong with the formula you're using. I'm
using the same EXACT formula.

Are you sure that 10:00 AM is entered as a TRUE time?

Would you like to see my test file?

Biff

>-----Original Message-----
>Thanks Biff, but I still have something wrong as I can't
get the data to
>fill is after 10:00 am. I have 286 rows of info and
Columns thru "S"
>The formula I used was:
>=IF(ISERROR(SMALL(IF((Schedule!A$2:A$286=A$1)*(Schedule!
B$2:B$286=A$3)*(Schedule!C$2:S$286=A$2),Column(A:Q)),ROW
(1:1))),"",INDEX(Schedule!C$1:S$1,SMALL(IF((Schedule!
A$2:A$286=A$1)*(Schedule!B$2:B$286=A43)*(Schedule!
C$2:S$286=A$2),Column(A:Q))),Row(1:1))))
>
>Can you see what I've done wrong?
>Thanks for your help! I do appreciate it very much.
>Kamille
>
>.
>

Nope, re-formated my time fields and it's working great now.
Thanks again for all your help!