Dim BedColumn as Integer
Dim Availability As Integer
Dim RoomName As String
Dim RoomNumber as Name 'this is a Named Range from excel's Name Manager
RoomName = RoomNumber.Name
BedColumn = Application.Evaluate("VLOOKUP(" & RoomName & ",LIST!C5:D50,2,FALSE)")
Availability = Application.Evaluate("VLOOKUP(B14,DATA!A1:EW36," & BedColumn & ",FALSE)")