Challenge Scenario
In hotel hospitality management and reservation desk operations, guests book different room types (Standard, Deluxe, or Suite) for various lengths of stay.
To prepare guest invoices, the front desk references a master nightly rate table using VLOOKUP and calculates total booking charges based on room rates and nights stayed.
As a Front Desk Coordinator, your tasks are:
- Rate / Night (Column D): Look up the nightly price for each Room Type (
C2) in the master rate table ($G$2:$H$4) using VLOOKUP(C2, $G$2:$H$4, 2, FALSE).
- Total Amount (Column E): Multiply Nights stayed by Rate / Night (
=B2 * D2).
- Grand Total Revenue (Cell E7): Sum all guest reservation totals using
SUM(E2:E5).
- Total Suite Bookings (Cell E8): Count how many guests booked a
"Suite" using COUNTIF(C2:C5, "Suite").
Your Sheet Layout
Understanding how the hotel reservation sheet is organized:
- Guest Bookings (Columns A to C): Shows Guest Name, Nights stayed (1 to 5 nights), and Room Type booked.
- Rate Card & Billing (Columns D & E): Looks up nightly price from the rate card and calculates total stay charges.
- Master Room Pricing Table (Columns G & H): The official room rate card: Standard ($120), Deluxe ($180), Suite ($300).
- Front Desk Revenue Summary (Rows 7 & 8): Summarizes grand total reservation revenue ($2,160) and suite room counts (2 bookings).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Guest Name, Nights, Room Type |
Guest Reservations |
Guest check-in information |
Read-only active hotel reservations. |
G2:H4 |
Room Type, Nightly Rate |
Master Rate Card |
Standard ($120), Deluxe ($180), Suite ($300) |
Master pricing lookup table. Lock with $G$2:$H$4. |
D2:D5 |
Rate / Night |
Lookup |
=VLOOKUP(C2, $G$2:$H$4, 2, FALSE) |
Looks up the nightly rate matching room type. |
E2:E5 |
Total Amount |
Calculation |
=B2 * D2 |
Multiplies nights by nightly rate. |
E7 |
GRAND TOTAL REVENUE |
Front Desk Summary |
=SUM(E2:E5) |
Sums all reservation revenue ($2,160). |
E8 |
TOTAL SUITE BOOKINGS |
Front Desk Summary |
=COUNTIF(C2:C5, "Suite") |
Counts how many guests booked suites (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), lookup Rate / Night using =VLOOKUP(C2, $G$2:$H$4, 2, FALSE).
2
In Column E (E2:E5), calculate Total Amount by multiplying Nights by Rate / Night (=B2*D2).
3
In cell E7, calculate grand total revenue using SUM(E2:E5).
4
In cell E8, count total suite bookings using COUNTIF(C2:C5, "Suite").
Solution & Formula Breakdown
Let's examine how VLOOKUP and billing multiplication work together:
1. Looking up Room Pricing with VLOOKUP (D2:D5)
=VLOOKUP(C2, $G$2:$H$4, 2, FALSE)
The VLOOKUP function searches Column G and returns the price from Column H (2nd column):
- James Gordon (Standard): Matches $120 → $120
- Rachel Green (Suite): Matches $300 → $300
- Lucas Scott (Deluxe): Matches $180 → $180
- Megan Fox (Suite): Matches $300 → $300
2. Calculating Total Stay Cost (E2:E5)
=B2 * D2
Multiply Nights stayed by Rate / Night:
- James Gordon:
3 * 120 = 360 → $360
- Rachel Green:
2 * 300 = 600 → $600
- Lucas Scott:
5 * 180 = 900 → $900
- Megan Fox:
1 * 300 = 300 → $300
3. Summary Rollups (E7 & E8)
- Grand Total Revenue (Cell E7):
=SUM(E2:E5) sums 360 + 600 + 900 + 300 = 2,160.
- Total Suite Bookings (Cell E8):
=COUNTIF(C2:C5, "Suite") returns 2.
The 4th argument FALSE in VLOOKUP is critical for text lookups. It guarantees exact spelling matches and prevents unpredictable nearest-match behavior!