PractiseSkills

Take the free test →

Practice by topic / Excel / Lookups

Lookups With Multiple Criteria Exercises

VLOOKUP searches one column, so for two criteria (region and quarter, product and size) you either build a helper key such as =A2&"-"&B2 and look that up, or use SUMIFS when the answer is a number. These exercises cover both, with XLOOKUP, INDEX/MATCH and IFERROR versions. These 10 exercises build up step by step, starting with build a helper key, vlookup on a helper key, xlookup on a helper key.

Start exercise 1 → Read the guide first

3 of 10 free · no sign-up needed · graded on the formula you write

1

Build a Helper Key

Free

A2 holds a region and B2 a quarter. Write a formula in C2 that joins them with a hyphen, e.g. 'North-Q1', to make a lookup key.

	A	B	C
1	Region	Quarter	Key
2	North	Q1	?

Show the solution

Use =A2&"-"&B2

Right — a helper key turns two criteria into one value that VLOOKUP can search for.

Expected result in C2: North-Q1

Try it in the live spreadsheet →

2

VLOOKUP on a Helper Key

Free

C2:C7 hold keys like 'North-Q1' and D2:D7 sales. F2 holds a region and G2 a quarter. Write a formula in H2 that looks up the sales for that region and quarter.

	A	B	C	D	E	F	G	H
1	Region	Quarter	Key	Sales		Region	Quarter	Sales
2	North	Q1	North-Q1	12000		South	Q2	?
3	North	Q2	North-Q2	13500
4	South	Q1	South-Q1	9800
5	South	Q2	South-Q2	11200
6	East	Q1	East-Q1	7600
7	East	Q2	East-Q2	8900

Show the solution

Use =VLOOKUP(F2&"-"&G2,C2:D7,2,FALSE)

Good — build the same key from F2 and G2 inside the VLOOKUP, then search the key column.

Expected result in H2: 11200

Try it in the live spreadsheet →

3

XLOOKUP on a Helper Key

Free

C2:C7 hold keys like 'North-Q1' and D2:D7 sales. F2 holds a region and G2 a quarter. Write a formula in H2 that uses XLOOKUP to return the sales for that combination.

	A	B	C	D	E	F	G	H
1	Region	Quarter	Key	Sales		Region	Quarter	Sales
2	North	Q1	North-Q1	12000		North	Q2	?
3	North	Q2	North-Q2	13500
4	South	Q1	South-Q1	9800
5	South	Q2	South-Q2	11200
6	East	Q1	East-Q1	7600
7	East	Q2	East-Q2	8900

Show the solution

Use =XLOOKUP(F2&"-"&G2,C2:C7,D2:D7)

Right — XLOOKUP takes the lookup column and the return column separately.

Expected result in H2: 13500

Try it in the live spreadsheet →

4

INDEX and MATCH on a Helper Key

C2:C7 hold keys like 'North-Q1' and D2:D7 sales. F2 holds a region and G2 a quarter. Write a formula in H2 that uses INDEX and MATCH to return the sales for that combination.

	A	B	C	D	E	F	G	H
1	Region	Quarter	Key	Sales		Region	Quarter	Sales
2	North	Q1	North-Q1	12000		East	Q1	?
3	North	Q2	North-Q2	13500
4	South	Q1	South-Q1	9800
5	South	Q2	South-Q2	11200
6	East	Q1	East-Q1	7600
7	East	Q2	East-Q2	8900

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

5

SUMIFS as a Two-Criteria Lookup

A2:A7 hold regions, B2:B7 quarters and C2:C7 sales, with no helper column. E2 holds a region and F2 a quarter. Write a formula in G2 that returns the sales for that combination.

	A	B	C	D	E	F	G
1	Region	Quarter	Sales		Region	Quarter	Sales
2	North	Q1	12000		East	Q2	?
3	North	Q2	13500
4	South	Q1	9800
5	South	Q2	11200
6	East	Q1	7600
7	East	Q2	8900

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

6

Price by Product and Size

C2:C7 hold keys like 'Tee-S' and D2:D7 prices. F2 holds a product and G2 a size. Write a formula in H2 that returns the price for that product and size.

	A	B	C	D	E	F	G	H
1	Product	Size	Key	Price		Product	Size	Price
2	Tee	S	Tee-S	15		Hoodie	L	?
3	Tee	M	Tee-M	15
4	Tee	L	Tee-L	17
5	Hoodie	S	Hoodie-S	35
6	Hoodie	M	Hoodie-M	35
7	Hoodie	L	Hoodie-L	38

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

7

Handle a Combination That Doesn't Exist

C2:C7 hold keys like 'North-Q1' and D2:D7 sales. F2 holds a region and G2 a quarter. Write a formula in H2 that returns the sales, or 'No data' if the combination isn't in the table.

	A	B	C	D	E	F	G	H
1	Region	Quarter	Key	Sales		Region	Quarter	Sales
2	North	Q1	North-Q1	12000		West	Q1	?
3	North	Q2	North-Q2	13500
4	South	Q1	South-Q1	9800
5	South	Q2	South-Q2	11200
6	East	Q1	East-Q1	7600
7	East	Q2	East-Q2	8900

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

8

Hours by Employee and Month

A2:A7 hold employees, B2:B7 months and C2:C7 hours worked. E2 holds an employee and F2 a month. Write a formula in G2 that returns the hours for that employee and month.

	A	B	C	D	E	F	G
1	Employee	Month	Hours		Employee	Month	Hours
2	Ali	Jan	160		Mei	Feb	?
3	Ali	Feb	152
4	Mei	Jan	148
5	Mei	Feb	170
6	Raj	Jan	155
7	Raj	Feb	140

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

9

Check Whether a Combination Exists

A2:A7 hold employees and B2:B7 months. E2 holds an employee and F2 a month. Write a formula in G2 that returns 'Found' if that employee and month appear together in the table, otherwise 'Not found'.

	A	B	C	D	E	F	G
1	Employee	Month	Hours		Employee	Month	Status
2	Ali	Jan	160		Raj	Mar	?
3	Ali	Feb	152
4	Mei	Jan	148
5	Mei	Feb	170
6	Raj	Jan	155
7	Raj	Feb	140

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →

10

Three Criteria at Once

A2:A7 hold regions, B2:B7 quarters, C2:C7 sales channels and D2:D7 sales. F2, G2 and H2 hold a region, quarter and channel. Write a formula in I2 that returns the sales for that combination.

	A	B	C	D	E	F	G	H	I
1	Region	Quarter	Channel	Sales		Region	Quarter	Channel	Sales
2	North	Q1	Online	5000		North	Q2	Online	?
3	North	Q1	Store	7000
4	North	Q2	Online	6500
5	South	Q1	Online	4300
6	South	Q1	Store	5500
7	North	Q2	Store	7000

Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).

Try it in the live spreadsheet →