Learning Excel: HLOOKUP

Looking up in a row

For rides to the Suburbs or the Docks, Kyle adds a surcharge. The surcharges aren't in a column, but side by side in a row. VLOOKUP has a little brother that looks sideways.

HLOOKUP

HLOOKUP works like VLOOKUP, but turned on its side: it looks in the first row of the table and gives what is below. So the third argument is a row number.

In H2, =HLOOKUP(C2,L9:N10,2,FALSE) looks for the zone Centre in L9:N9 and gives the surcharge from the second row: £0.

=HLOOKUP(C2,L9:N10,2,FALSE)

	A	B	C	D	E	F	G	H	I	K	L	M	N	P	Q
1	Ride	Customer	Zone	Km	Weight	Price	Area	Surcharge	Customer name	Name	Code	Area		From km	Price
2	R01	K03	Centre	2.5	3			£0.00		Bloom Bakery	K01	Manchester		0	£6.00
3	R02	K01	Suburbs	7.2	12					Book Nook	K02	Burnage		3	£9.00
4	R03	K05	Docks	11.4	25					Cygnet Café	K03	Manchester		6	£12.00
5	R04	K02	Centre	1.8	1					Dot Print	K04	Didsbury		10	£18.00
6	R05	K03	Suburbs	5.5	8					Fork & Spoke	K05	Withington
7	R06	K04	Docks	9.9	18
8	R07	K01	Centre	3	4
9	R08	K02	Suburbs	6	10					Zone	Centre	Suburbs	Docks
10	R09	K05	Docks	14	30					Surcharge	£0.00	£2.00	£5.00
11	R10	K06	Centre	4.2	2
12
13									Position	Weight	Centre	Suburbs	Docks	Class	Medium
14										Light	£0.00	£1.00	£3.00	Zone	Docks
15										Medium	£2.00	£3.00	£5.00	Result
16										Heavy	£4.00	£6.00	£8.00
17

Try it yourself

Ride R03 goes to the Docks. Look up the surcharge in H4.

	A	B	C	D	E	F	G	H	I	K	L	M	N	P	Q
1	Ride	Customer	Zone	Km	Weight	Price	Area	Surcharge	Customer name	Name	Code	Area		From km	Price
2	R01	K03	Centre	2.5	3					Bloom Bakery	K01	Manchester		0	£6.00
3	R02	K01	Suburbs	7.2	12					Book Nook	K02	Burnage		3	£9.00
4	R03	K05	Docks	11.4	25					Cygnet Café	K03	Manchester		6	£12.00
5	R04	K02	Centre	1.8	1					Dot Print	K04	Didsbury		10	£18.00
6	R05	K03	Suburbs	5.5	8					Fork & Spoke	K05	Withington
7	R06	K04	Docks	9.9	18
8	R07	K01	Centre	3	4
9	R08	K02	Suburbs	6	10					Zone	Centre	Suburbs	Docks
10	R09	K05	Docks	14	30					Surcharge	£0.00	£2.00	£5.00
11	R10	K06	Centre	4.2	2
12
13									Position	Weight	Centre	Suburbs	Docks	Class	Medium
14										Light	£0.00	£1.00	£3.00	Zone	Docks
15										Medium	£2.00	£3.00	£5.00	Result
16										Heavy	£4.00	£6.00	£8.00
17

My Excel writes

Ready for more?

The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson