Google Sheets Fundamentals Course: IF, COUNTIF and lookups

Untimed topic quiz: 17 questions on IF, COUNTIF and lookups, with an explanation after each answer. Questions and answers are shuffled every time.

Course
Canada
100 questions
Checked October 13, 2026
Fact-checked October 13, 2026

Start with the lessonsGoogle Sheets Fundamentals Course: Lessons: about 120 minutes of reading. Every test question is covered in the lessons.

Topic quiz 4: IF, COUNTIF and lookups

Question 1 of 17
What is the correct order of the parts in an IF function?
- IF(value_if_true, logical_expression, value_if_false)
- IF(logical_expression, value_if_false, value_if_true)
- IF(value_if_true, value_if_false, logical_expression)
- IF(logical_expression, value_if_true, value_if_false)
Show answer
Answer: D. IF(logical_expression, value_if_true, value_if_false)
The test comes first, then the value if TRUE, then the value if FALSE. Google says putting the two values in the wrong order is the most common IF mistake.
Question 2 of 17
In the inventory below, what does =IF(C3>=D3,"OK","Reorder") return?
A B C D
1 Item Category Qty Reorder at
2 Nitrile gloves PPE 120 50
3 Safety glasses PPE 18 20
4 Box cutters Tools 9 10
5 Packing tape Supplies 64 24
6 Hi-vis vests PPE 30 15
7 Label rolls Supplies 6 12
8 Tape guns Tools 14 5
- TRUE
- Reorder
- FALSE
- OK
Show answer
Answer: B. Reorder
C3 is 18 and D3 is 20. 18 is not greater than or equal to 20, so the test is FALSE and IF returns "Reorder".
Question 3 of 17
Cell B2 holds 6. What does =IF(B2>=8,"Full",IF(B2>=4,"Part","Off")) return?
- Off
- Full
- Part
- FALSE
Show answer
Answer: C. Part
The first test (6 >= 8) is FALSE, so Sheets uses the inner IF. Its test (6 >= 4) is TRUE, so the result is "Part".
Question 4 of 17
In the attendance sheet below, P is present, A is absent and L is late. What does =COUNTIF(B2:F6,"A") return?
A B C D E F
1 Name Mon Tue Wed Thu Fri
2 Arjun P P A P P
3 Bea P L P P A
4 Chloe A A P P P
5 Dev P P P p P
6 Elif P P L A P
- 5
- 4
- 2
- 25
Show answer
Answer: A. 5
COUNTIF counts the cells equal to "A": Arjun 1, Bea 1, Chloe 2, Elif 1, for a total of 5.
Question 5 of 17
In the attendance sheet below, Dev's Thursday was typed as a small "p". What does =COUNTIF(B5:F5,"P") return?
A B C D E F
1 Name Mon Tue Wed Thu Fri
2 Arjun P P A P P
3 Bea P L P P A
4 Chloe A A P P P
5 Dev P P P p P
6 Elif P P L A P
- 0
- 5
- 4
- 1
Show answer
Answer: B. 5
COUNTIF is not case sensitive, so the small p matches "P" too. All five of Dev's days are counted.
Question 6 of 17
In the inventory below, what does =COUNTIF(C2:C8,">20") return?
A B C D
1 Item Category Qty Reorder at
2 Nitrile gloves PPE 120 50
3 Safety glasses PPE 18 20
4 Box cutters Tools 9 10
5 Packing tape Supplies 64 24
6 Hi-vis vests PPE 30 15
7 Label rolls Supplies 6 12
8 Tape guns Tools 14 5
- 3
- 4
- 214
- 2
Show answer
Answer: A. 3
The quantities greater than 20 are 120, 64 and 30. 18 is not greater than 20, so the count is 3.
Question 7 of 17
In the inventory below, what does =COUNTIF(A2:A8,"*tape*") return?
A B C D
1 Item Category Qty Reorder at
2 Nitrile gloves PPE 120 50
3 Safety glasses PPE 18 20
4 Box cutters Tools 9 10
5 Packing tape Supplies 64 24
6 Hi-vis vests PPE 30 15
7 Label rolls Supplies 6 12
8 Tape guns Tools 14 5
- 0
- 1
- 2
- 3
Show answer
Answer: C. 2
The * wildcard matches any characters, and COUNTIF is not case sensitive. "Packing tape" and "Tape guns" both contain "tape".
Question 8 of 17
In the inventory below, what does =SUMIF(B2:B8,"PPE",C2:C8) return?
A B C D
1 Item Category Qty Reorder at
2 Nitrile gloves PPE 120 50
3 Safety glasses PPE 18 20
4 Box cutters Tools 9 10
5 Packing tape Supplies 64 24
6 Hi-vis vests PPE 30 15
7 Label rolls Supplies 6 12
8 Tape guns Tools 14 5
- 150
- 168
- 261
- 3
Show answer
Answer: B. 168
SUMIF adds the Qty cells in rows where the Category is PPE: 120 + 18 + 30 = 168.
Question 9 of 17
Cell D2 holds =B2/C2, and C2 is sometimes empty, which gives an error. Which formula shows 0 instead of the error?
- =IF(B2/C2,0)
- =COUNTIF(B2/C2,0)
- =IFERROR(B2/C2,0)
- =ROUND(B2/C2,0)
Show answer
Answer: C. =IFERROR(B2/C2,0)
IFERROR returns the first value if it is not an error. If it is an error, it returns the second value, here 0.
Question 10 of 17
In the price list below, what does =VLOOKUP("H310",A2:D6,3,FALSE) return?
A B C D
1 Code Item Price Stock
2 K100 Keyboard 34.99 12
3 M220 Mouse 18.50 40
4 H310 Headset 59.00 7
5 C415 USB cable 9.25 65
6 W520 Webcam 72.00 0
- 59.00
- 7
- H310
- Headset
Show answer
Answer: A. 59.00
VLOOKUP finds H310 in the first column and returns the value from column 3 of the range, which is the Price: 59.00.
Question 11 of 17
In the price list below, what does =VLOOKUP("M220",A2:D6,4,FALSE) return?
A B C D
1 Code Item Price Stock
2 K100 Keyboard 34.99 12
3 M220 Mouse 18.50 40
4 H310 Headset 59.00 7
5 C415 USB cable 9.25 65
6 W520 Webcam 72.00 0
- 18.50
- 40
- 12
- Mouse
Show answer
Answer: B. 40
The index counts from 1 at the first column of the range, so column 4 is Stock. The Stock for M220 is 40.
Question 12 of 17
If you leave out the is_sorted argument in VLOOKUP, what kind of match does Google Sheets use?
- Approximate match (is_sorted is TRUE)
- Wildcard match
- Exact match (is_sorted is FALSE)
- It shows an error until you add it
Show answer
Answer: A. Approximate match (is_sorted is TRUE)
TRUE (approximate match) is the default. Google recommends FALSE for an exact match.
Question 13 of 17
In the price list below, what does =VLOOKUP("Mouse",A2:D6,3,FALSE) return?
A B C D
1 Code Item Price Stock
2 K100 Keyboard 34.99 12
3 M220 Mouse 18.50 40
4 H310 Headset 59.00 7
5 C415 USB cable 9.25 65
6 W520 Webcam 72.00 0
- 18.50
- 40
- #N/A
- M220
Show answer
Answer: C. #N/A
The search key must be in the first column of the range. "Mouse" is in column B, not column A, so no exact match is found and VLOOKUP returns #N/A.
Question 14 of 17
A VLOOKUP for "Mouse" returns #N/A, but the list shows a cell that looks like Mouse. What is a likely cause that Google warns about?
- The list has more than 100 rows
- The cell has an extra space, such as "Mouse "
- The sheet is shared with a Commenter
- The function name was typed in small letters
Show answer
Answer: B. The cell has an extra space, such as "Mouse "
Leading and trailing spaces matter: "Mouse" and "Mouse " are different values. Trim the spaces before using VLOOKUP.
Question 15 of 17
In the price list below, what does =XLOOKUP("Webcam",B2:B6,A2:A6) return?
A B C D
1 Code Item Price Stock
2 K100 Keyboard 34.99 12
3 M220 Mouse 18.50 40
4 H310 Headset 59.00 7
5 C415 USB cable 9.25 65
6 W520 Webcam 72.00 0
- #N/A
- W520
- 72.00
- 0
Show answer
Answer: B. W520
XLOOKUP finds Webcam in B2:B6 and returns the value in the same position of A2:A6. Unlike VLOOKUP, the result can be to the left of the search column.
Question 16 of 17
In the price list below, what does =XLOOKUP("Z999",A2:A6,C2:C6,"Not found") return?
A B C D
1 Code Item Price Stock
2 K100 Keyboard 34.99 12
3 M220 Mouse 18.50 40
4 H310 Headset 59.00 7
5 C415 USB cable 9.25 65
6 W520 Webcam 72.00 0
- #N/A
- 72.00
- Not found
- 0
Show answer
Answer: C. Not found
Z999 is not in A2:A6, so XLOOKUP returns the missing_value given in the fourth argument: "Not found".
Question 17 of 17
If you do not give XLOOKUP a match_mode, how does it match?
- Wildcard match (match_mode 2)
- Approximate match, like VLOOKUP's default
- Exact match (match_mode 0)
- The next smaller value (match_mode -1)
Show answer
Answer: C. Exact match (match_mode 0)
match_mode is optional and its default is 0, which means exact match.

	A	B	C	D
1	Item	Category	Qty	Reorder at
2	Nitrile gloves	PPE	120	50
3	Safety glasses	PPE	18	20
4	Box cutters	Tools	9	10
5	Packing tape	Supplies	64	24
6	Hi-vis vests	PPE	30	15
7	Label rolls	Supplies	6	12
8	Tape guns	Tools	14	5

	A	B	C	D	E	F
1	Name	Mon	Tue	Wed	Thu	Fri
2	Arjun	P	P	A	P	P
3	Bea	P	L	P	P	A
4	Chloe	A	A	P	P	P
5	Dev	P	P	P	p	P
6	Elif	P	P	L	A	P

	A	B	C	D
1	Code	Item	Price	Stock
2	K100	Keyboard	34.99	12
3	M220	Mouse	18.50	40
4	H310	Headset	59.00	7
5	C415	USB cable	9.25	65
6	W520	Webcam	72.00	0

Topic quiz 4: IF, COUNTIF and lookups

Related tests