Google Sheets Test Google Sheets Advanced Lookup Functions 3 — Questions and Answers
Question 1: In XLOOKUP, which argument controls whether the search runs from first-to-last or last-to-first?
- match_mode
- search_mode (Correct answer)
- return_array
- if_not_found
Correct answer: search_mode
The search_mode argument accepts values like 1, -1, 2, and -2 to set search direction and binary search.
Question 2: How does INDEX(range, MATCH(...)) find the intersection of a row and column?
- By using MATCH twice for row and column positions (Correct answer)
- By nesting VLOOKUP inside INDEX
- By using ARRAYFORMULA
- By sorting the range first
Correct answer: By using MATCH twice for row and column positions
A two-dimensional lookup uses INDEX with two MATCH calls, one for the row and one for the column.
Question 3: What is the effect of setting XLOOKUP's match_mode to 2?
- Exact match only
- Wildcard character match (Correct answer)
- Approximate smaller match
- Binary search descending
Correct answer: Wildcard character match
match_mode 2 enables wildcard matching using ? and * characters.
Question 4: Which function returns multiple matching rows for a single criterion, unlike VLOOKUP which returns only the first?
- FILTER (Correct answer)
- MATCH
- HLOOKUP
- LOOKUP
Correct answer: FILTER
FILTER returns every row that meets the condition, not just the first match.
Question 5: When VLOOKUP's range_lookup is TRUE, how must the first column be arranged for correct results?
- Sorted in ascending order (Correct answer)
- Sorted in descending order
- Containing no duplicates
- Formatted as text
Correct answer: Sorted in ascending order
Approximate-match VLOOKUP requires the lookup column sorted ascending, or it returns incorrect values.
Question 6: What does the QUERY function use to retrieve lookup-style results from a dataset?
- A SQL-like query language (Correct answer)
- Regular expressions only
- The MATCH position
- Column index numbers
Correct answer: A SQL-like query language
QUERY uses a Google Visualization API query language similar to SQL for selecting and filtering data.
Question 7: How can you make VLOOKUP work across an entire column without manually copying the formula down?
- Wrap it in ARRAYFORMULA (Correct answer)
- Use the FALSE argument
- Add a dollar sign to the index
- Sort the source data
Correct answer: Wrap it in ARRAYFORMULA
ARRAYFORMULA lets a single VLOOKUP process an entire array of lookup values at once.
Google Sheets Test Google Sheets Advanced Lookup Functions 3
In XLOOKUP, which argument controls whether the search runs from first-to-last or last-to-first?