IF/AND VLOOKUP question...

[Kevin]

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?

 

[Max]

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.

 

[Kevin]

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

 

[Max]

Welcome, and glad it worked fine for you, Kevin.
Appreciate the feedback.