Learning Excel: XLOOKUP

Looking up in any direction

On the delivery slips Kyle wants the customer's name, and that's to the left of the code. VLOOKUP can't reach it. XLOOKUP can, and it also knows what to do with a customer who isn't on the list yet.

XLOOKUP

XLOOKUP takes two separate ranges: where you search and where you take the answer from. So it doesn't matter whether the answer is to the left or to the right.

In I2, =XLOOKUP(B2,L2:L6,K2:K6) looks for the code K03 in L2:L6 and takes the name in the same place in K2:K6: Cygnet Café.

=XLOOKUP(B2,L2:L6,K2:K6)

	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				Cygnet Café	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

When nothing turns up

Customer K06 of ride R10 isn't on the list yet, so XLOOKUP gives #N/A for that ride.

A fourth argument says what should appear when nothing is found: =XLOOKUP(B11,L2:L6,K2:K6,"New customer").

I11

=XLOOKUP(B11,L2:L6,K2:K6,"New customer")

	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				New customer
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

Who is the customer of ride R02? Look up the name in I3.

	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 5 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson