Google Sheets Fundamentals Course: IF, COUNTIF and lookups

1. السؤال 1 من 17

   What is the correct order of the parts in an IF function?

   • AIF(value_if_true, logical_expression, value_if_false)
   • BIF(logical_expression, value_if_false, value_if_true)
   • CIF(value_if_true, value_if_false, logical_expression)
   • DIF(logical_expression, value_if_true, value_if_false)
   أظهر الإجابة

   الإجابة: 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.

2. السؤال 2 من 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

   • ATRUE
   • BReorder
   • CFALSE
   • DOK
   أظهر الإجابة

   الإجابة: 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".

3. السؤال 3 من 17

   Cell B2 holds 6. What does =IF(B2>=8,"Full",IF(B2>=4,"Part","Off")) return?

   • AOff
   • BFull
   • CPart
   • DFALSE
   أظهر الإجابة

   الإجابة: 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".

4. السؤال 4 من 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

   • A5
   • B4
   • C2
   • D25
   أظهر الإجابة

   الإجابة: A. 5

   COUNTIF counts the cells equal to "A": Arjun 1, Bea 1, Chloe 2, Elif 1, for a total of 5.

5. السؤال 5 من 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

   • A0
   • B5
   • C4
   • D1
   أظهر الإجابة

   الإجابة: B. 5

   COUNTIF is not case sensitive, so the small p matches "P" too. All five of Dev's days are counted.

6. السؤال 6 من 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

   • A3
   • B4
   • C214
   • D2
   أظهر الإجابة

   الإجابة: A. 3

   The quantities greater than 20 are 120, 64 and 30. 18 is not greater than 20, so the count is 3.

7. السؤال 7 من 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

   • A0
   • B1
   • C2
   • D3
   أظهر الإجابة

   الإجابة: C. 2

   The * wildcard matches any characters, and COUNTIF is not case sensitive. "Packing tape" and "Tape guns" both contain "tape".

8. السؤال 8 من 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

   • A150
   • B168
   • C261
   • D3
   أظهر الإجابة

   الإجابة: B. 168

   SUMIF adds the Qty cells in rows where the Category is PPE: 120 + 18 + 30 = 168.

9. السؤال 9 من 17

   Cell D2 holds =B2/C2, and C2 is sometimes empty, which gives an error. Which formula shows 0 instead of the error?

   • A=IF(B2/C2,0)
   • B=COUNTIF(B2/C2,0)
   • C=IFERROR(B2/C2,0)
   • D=ROUND(B2/C2,0)
   أظهر الإجابة

   الإجابة: 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.

10. السؤال 10 من 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

    • A59.00
    • B7
    • CH310
    • DHeadset
    أظهر الإجابة

    الإجابة: 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.

11. السؤال 11 من 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

    • A18.50
    • B40
    • C12
    • DMouse
    أظهر الإجابة

    الإجابة: 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.

12. السؤال 12 من 17

    If you leave out the is_sorted argument in VLOOKUP, what kind of match does Google Sheets use?

    • AApproximate match (is_sorted is TRUE)
    • BWildcard match
    • CExact match (is_sorted is FALSE)
    • DIt shows an error until you add it
    أظهر الإجابة

    الإجابة: A. Approximate match (is_sorted is TRUE)

    TRUE (approximate match) is the default. Google recommends FALSE for an exact match.

13. السؤال 13 من 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

    • A18.50
    • B40
    • C#N/A
    • DM220
    أظهر الإجابة

    الإجابة: 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.

14. السؤال 14 من 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?

    • AThe list has more than 100 rows
    • BThe cell has an extra space, such as "Mouse "
    • CThe sheet is shared with a Commenter
    • DThe function name was typed in small letters
    أظهر الإجابة

    الإجابة: 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.

15. السؤال 15 من 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

    • A#N/A
    • BW520
    • C72.00
    • D0
    أظهر الإجابة

    الإجابة: 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.

16. السؤال 16 من 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

    • A#N/A
    • B72.00
    • CNot found
    • D0
    أظهر الإجابة

    الإجابة: C. Not found

    Z999 is not in A2:A6, so XLOOKUP returns the missing_value given in the fourth argument: "Not found".

17. السؤال 17 من 17

    If you do not give XLOOKUP a match_mode, how does it match?

    • AWildcard match (match_mode 2)
    • BApproximate match, like VLOOKUP's default
    • CExact match (match_mode 0)
    • DThe next smaller value (match_mode -1)
    أظهر الإجابة

    الإجابة: C. Exact match (match_mode 0)

    match_mode is optional and its default is 0, which means exact match.

لوحة المفاتيح: 1–4 للإجابة، وEnter للسؤال التالي.