Lookup & reference › HLOOKUP79 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeHLOOKUP

HLOOKUP: room rates from a horizontal table

Mrs. Rahma's guest house in Bukittinggi keeps its room rates in a horizontal table: room types across the top row, with weekday and weekend rates below. The receptionist wants the weekday rate to appear automatically on the guest list.

Task

Fill C2:C6 with the weekday rate for the room type in column B. The rates are in the table G1:I3: row 1 is the room type, row 2 the weekday rate and row 3 the weekend rate. Use HLOOKUP with an exact match.

Cells to fillC2:C6

Required functionsHLOOKUP

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
HLOOKUP works like VLOOKUP, but it searches the first row of the table and then goes down to the n-th row.
Hint 2
The weekday rate is in row 2 of the table. Lock the table as $G$1:$I$3 and use FALSE for an exact match.
Hint 3
Pattern: =HLOOKUP(B2, $G$1:$I$3, …, FALSE).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C6

=HLOOKUP(B2,$G$1:$I$3,2,FALSE)

Write it in C2, then fill down to C6.

Explanation

=HLOOKUP(B2,$G$1:$I$3,2,FALSE) finds "Deluxe" in row 1 of the table, then returns the value in row 2 of the table in the same column. For the weekend rate, just change 2 to 3. The "H" stands for horizontal; if your table runs downward, use VLOOKUP. In Excel 365, XLOOKUP reads horizontal and vertical tables the same way.

Starting worksheet: Sheet1
Row	A	B	C	F	G	H	I
1	Guest	Room type	Weekday rate	Type	Standard	Deluxe	Suite
2	Andre	Deluxe		Weekday	450,000	650,000	1,100,000
3	Bella	Standard		Weekend	550,000	800,000	1,350,000
4	Pak Wahyu	Suite
5	Mbak Tari	Standard
6	Yudha	Deluxe
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result