If it's the same date, won't it be the right day of week?

Put this in S2 of Sheet1.

=isnumber(match(1,(e2=sheet2!a2:a999)*(H2=Sheet2!d 2:d999)
*(m2=sheet2!h2:h999)*(n2<=sheet2!i2:i999),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And you can just keep adding more conditions inside that product area. Just
wrap the expression in ()'s.

False means it didn't find any row that matched all the criteria at the same
time. True means that it found a row that matched all that stuff.

Jules wrote:

Hi everyone! I need help with this formula, I tried an if formula, but it's
way past my ability and thought I might try the discussion group...any help
would be great.

WORK SHEET ONE

C2 Patient
D2 Surgeon
E2 Group
F2 empty
G2 day of week
H2 date of service
L2 OR Suite
M2 Time in room
N2 Time out
O2 total minutes
P2 total time
R2 service

WORK SHEET TWO

A2 GROUP
B2 OR
D2 DATE OF BLOCK
E2 DATE CODE
F2 WEEK OF MONTH
G2 DAY OF WEEK
H2 BLOCK START
I2 BLOCK END

What I need to do is determine if a surgery in worksheet one meets the
criteria of the surgeons block time...is it the right group, the right date,
the right day of week , is it equal to or after the start time and be for the
end time?

If the above is true, another thing I need but do not need to have in the
same formula, is it the right room number?

Any suggestions would be wonderful!

Thanks so much and have a great day.
--
Jules

--

Dave Peterson