Learning Excel: INDEX and MATCH

The dream team

MATCH finds the position, INDEX fetches what's there. Together they look in any direction, and even in two directions at once. Then Kyle no longer has to count the row numbers himself.

INDEX with MATCH

Put MATCH where the row number goes in INDEX. In I2, =INDEX(K2:K6,MATCH(B2,L2:L6,0)) first finds the position of K03 in the codes (3), then takes the third name: Cygnet Café.

Just like XLOOKUP, this duo is happy to look to the left.

=INDEX(K2:K6,MATCH(B2,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				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

In two directions

In a table with rows and columns, you find both the row and the column with MATCH:

=INDEX(L14:N16,MATCH("Heavy",K14:K16,0),MATCH("Suburbs",L13:N13,0)) finds Heavy on row 3 and Suburbs in column 2, and gives £6.

Q15

=INDEX(L14:N16,MATCH("Heavy",K14:K16,0),MATCH("Suburbs",L13:N13,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										Light	£0.00	£1.00	£3.00	Zone	Docks
15										Medium	£2.00	£3.00	£5.00	Result	£6.00
16										Heavy	£4.00	£6.00	£8.00
17

Try it yourself

Which area is the customer of ride R03 in? Look it up in G4 with INDEX and MATCH.

	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