=INDEX(surgeon_range,MATCH(1,(date_range=date_of_s urgery)*(room_range=room_o
f_surgery),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jules" wrote in message
...
I have two worksheets...I need to match serveral criteria...I have tried
match, index, vlookup...to no avail.

I have to match the surgeon with the correct day and time his/her room is
available to the time in which they preformed the surgery.

so, surgeon block (time they reserve) with the time they do surgery (a
seperate worksheet) contianing surgeon, date, time of case.

anyone know what formula or function I can use?

Thanks os much for the time.
--
Jules