Google Sheets Test Google Sheets Advanced Lookup Functions 2 — Questions and Answers
Question 1: In the formula =VLOOKUP(A2, D2:F10, 3, FALSE), what does the FALSE argument specify?
- An exact match is required (Correct answer)
- The search should be approximate
- Results should be sorted
- The column index is invalid
Correct answer: An exact match is required
FALSE (or 0) tells VLOOKUP to find an exact match rather than the nearest smaller value.
Question 2: Which function is generally preferred over VLOOKUP because it can look both left and right of the search column?
- HLOOKUP
- INDEX/MATCH (Correct answer)
- LOOKUP array
- FILTER
Correct answer: INDEX/MATCH
INDEX/MATCH can return values from any column, including those to the left of the lookup column.
Question 3: What will VLOOKUP return if the lookup value is not found and range_lookup is FALSE?
- 0
- The closest value
- #N/A (Correct answer)
- A blank cell
Correct answer: #N/A
An exact-match VLOOKUP returns #N/A when no matching value exists.
Question 4: In XLOOKUP, what is the purpose of the fourth (optional) argument?
- The lookup column width
- The value to return if no match is found (Correct answer)
- The sort order
- The number of results
Correct answer: The value to return if no match is found
XLOOKUP's fourth argument sets a default return value when the lookup fails, replacing IFERROR wrapping.
Question 5: Why does VLOOKUP break when a new column is inserted inside the table range?
- The column index number no longer points to the intended column (Correct answer)
- VLOOKUP cannot handle more than five columns
- It converts numbers to text
- The lookup value is deleted
Correct answer: The column index number no longer points to the intended column
VLOOKUP uses a fixed numeric column index, so inserting a column shifts the target and returns wrong data.
Question 6: Which function pair lets you perform a horizontal lookup across a row of headers?
- VLOOKUP
- HLOOKUP (Correct answer)
- INDEX/COUNT
- SUMIF
Correct answer: HLOOKUP
HLOOKUP searches across the top row and returns a value from a specified row beneath it.
Question 7: What does the MATCH function return?
- The value at a cell reference
- The relative position of a value within a range (Correct answer)
- The sum of matched values
- TRUE or FALSE
Correct answer: The relative position of a value within a range
MATCH returns the numeric position of a lookup value within a row or column.
Google Sheets Test Google Sheets Advanced Lookup Functions 2
In the formula =VLOOKUP(A2, D2:F10, 3, FALSE), what does the FALSE argument specify?