Learning Excel: MATCH

Which position?

Sometimes you don't want to know what's there, but where. "How far down the list is K04?" MATCH doesn't give back a value, but a position. That sounds modest, but in the next lessons it turns out to be worth its weight in gold.

MATCH

MATCH looks for a value in a row or a column and gives back its position: 1 for the first cell, 2 for the second. With 0 as the third argument it looks for an exact match.

I14 holds =MATCH("K04",L2:L6,0): K04 is in fourth place in L2:L6, so the result is 4.

I14

=MATCH("K04",L2:L6,0)

	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									4	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

Find the position of the zone from Q14 in the row L13:N13. Put it in I14.

	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

I14

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