Google Sheets Test Google Sheets Formula 3 — Questions and Answers
Question 1: Which formula looks up "Apple" in column A and returns the matching value from column C?
- =VLOOKUP("Apple",A:C,3,FALSE) (Correct answer)
- =VLOOKUP("Apple",A:C,1,FALSE)
- =HLOOKUP("Apple",A:C,3,FALSE)
- =VLOOKUP(3,A:C,"Apple",FALSE)
Correct answer: =VLOOKUP("Apple",A:C,3,FALSE)
VLOOKUP searches column A and returns column 3 (C) with an exact match.
Question 2: What does the FALSE argument in VLOOKUP request?
- An exact match (Correct answer)
- An approximate match
- A reverse search
- A case-sensitive match
Correct answer: An exact match
FALSE (or 0) forces VLOOKUP to require an exact match.
Question 3: Which formula sums B1:B10 only where A1:A10 equals "North"?
- =SUMIF(A1:A10,"North",B1:B10) (Correct answer)
- =SUMIF(B1:B10,"North",A1:A10)
- =COUNTIF(A1:A10,"North")
- =SUM(A1:A10,"North")
Correct answer: =SUMIF(A1:A10,"North",B1:B10)
SUMIF checks the range, matches the criterion, then sums the sum_range.
Question 4: What does =INDEX(A1:A10,3) return?
- The 3rd value in A1:A10 (Correct answer)
- The value in cell C1
- The sum of the first 3 cells
- The 3rd largest value
Correct answer: The 3rd value in A1:A10
INDEX returns the item at the given position within a range.
Question 5: Which function finds the position of "Blue" within the range A1:A5?
- =MATCH("Blue",A1:A5,0) (Correct answer)
- =FIND("Blue",A1:A5)
- =INDEX("Blue",A1:A5)
- =SEARCH(A1:A5,"Blue")
Correct answer: =MATCH("Blue",A1:A5,0)
MATCH returns the relative position of a value in a range.
Question 6: What is a key advantage of INDEX/MATCH over VLOOKUP?
- It can look up values to the left of the search column (Correct answer)
- It cannot handle text
- It only works on one cell
- It requires sorted data
Correct answer: It can look up values to the left of the search column
INDEX/MATCH can return columns to the left, which VLOOKUP cannot.
Question 7: What does =XLOOKUP("Red",A1:A5,B1:B5) return if "Red" is not found and no default is given?
- #N/A (Correct answer)
- 0
- An empty string
- FALSE
Correct answer: #N/A
Without an if_not_found argument, XLOOKUP returns #N/A.
Google Sheets Test Google Sheets Formula 3
Which formula looks up "Apple" in column A and returns the matching value from column C?