Update value of cell based on conditions met in another table

[cmspeed]

Hello -

I have a room and class schedule that must be built in Excel (I'd rather use a database) and I am wondering if it is possible to change the value of a cell in one table based on whether two cells in the same row meet certain conditions in another table. I have created a watered-down version of my tables below. First is the schedule. In Sheet1, I have the class period in Column A and the Room in Column B.

Sheet1

I would like to put an X in the corresponding cells in Sheet2 indicating a room is in use during a particular period. 

Sheet2

So Sheet2 should look like this as a result:

Right now, I sort Sheet1 by room and period and manually update Sheet2. But obviously if I make a change this is clunky and I need to see which rooms are available during a particular period. This is why I'd rather use a database but can't for a few reasons. 

I experimented with IF formulas such as

=IF(AND(Sheet1!A$1=Sheet2!B1,Sheet1!B$1=Sheet2!A2),"X","") 

But that didn't work. Returned blanks for all I know the absolute/relative values are part of the issue. (still didn't work with $A1 and $B1, not that I'd expect it to). The other tricky part is it has to be the same row. There are lots of rooms used each period and lots of periods used by each room, so it can't just be if "1" shows up in Column A and "Room A" shows up in Column B. Then everything would always be checked off.

Any help would be appreciated. Thank you!

[#2 · Accepted solution]

@cmspeed 

=IF(NOT(ISNA(VLOOKUP(B$1&$A2,sheet1!$A$2:$A$10&sheet1!$B$2:$B$10,1,FALSE))),"X","")

You can try this formula which seems to work in my sheet. I've entered the formula in cell B2 and copied it across range B2:D5. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

[#3]

Yes! Thank you! I had to remove the table formatting to get it to work properly but my actual schedule isn't formatted as a table, anyway. Now to reverse-engineer it to make it work in my actual schedule. :) Thank you so much!!!