Hi, here's what I'm looking for... column A lists days of the week. Column E
list room numbers. Column C lists codes. This data will change often and may
list days, codes or rooms more than once. I would like to set a condition
such as: if a row contains "Monday" and Room "1" then return the code in
column C from the same row. This was easy to do when I chose specific cells,
but because the info may change within the columns, I need to formula to look
at whole columns or ranges. So, anytime a row matches Monday and room 1,
wherever it is, column C code is returned. I've come close using IF ((AND
statement but it won't allow me to use ranges...any ideas?

Assume source data as described starts in row2 down

Assume inputs for the day and the room number
will be made in G2:H2 down, eg
in G2: Monday
in H2: 1

then you could place in I2, and array-enter the formula by pressing
CTRL+SHIFT+ENTER:
=IF(COUNTA(G2:H2)<2,"",INDEX(C$2:C$100,MATCH(1,(A$ 2:A$100=G2)*(E$2:E$100=H2),0)))
Copy I2 down as far as required. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kevin" wrote:
Hi, here's what I'm looking for... column A lists days of the week. Column E
list room numbers. Column C lists codes. This data will change often and may
list days, codes or rooms more than once. I would like to set a condition
such as: if a row contains "Monday" and Room "1" then return the code in
column C from the same row. This was easy to do when I chose specific cells,
but because the info may change within the columns, I need to formula to look
at whole columns or ranges. So, anytime a row matches Monday and room 1,
wherever it is, column C code is returned. I've come close using IF ((AND
statement but it won't allow me to use ranges...any ideas?

Thank you Max, it worked fine. As always, your fast, accurate response made
someone else's life a lot easier. Much appreciated, -Kevin

Welcome, and glad it worked fine for you, Kevin.
Appreciate the feedback.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kevin" wrote in message
...
Thank you Max, it worked fine. As always,
your fast, accurate response made
someone else's life a lot easier. Much appreciated, -Kevin