Linking Data (look At Sheet 1 And Populate The Date Columns With The Guest Name From Sheet 1)

Sheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.

In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.

Name Sat Sun Mon Tue Wed Thu Fri

Mr. Smith 1A 1A 1A 1A
Mr. Jones 3B 3B 3B
Ms. Tiller 4A 4A 4A 4A 4A 4A 4A

Sheet 2 is in the same workbook and looks something like this:

Room # Sat Sun Mon Tue Wed Thu Fri
1A
1B
2A
2B
3A
3B

I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1

I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available")
It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.