Google Sheets Advanced Lookup Functions Flashcards

Which XLOOKUP search_mode value performs a binary search on data sorted in ascending order?

Answer: 2

search_mode 2 runs a binary search assuming the lookup array is sorted ascending, which is faster on large sorted data.

What does LOOKUP (the plain LOOKUP function) assume about the data it searches?

Answer: It is sorted in ascending order

The legacy LOOKUP function assumes ascending-sorted data and can return wrong results otherwise.

To return the LAST matching value instead of the first, which XLOOKUP argument do you adjust?

Answer: Set search_mode to -1

search_mode -1 searches from last to first, returning the final match instead of the first.

When using FILTER for a lookup, what does it return if no rows meet the condition?

Answer: #N/A error

FILTER returns #N/A when no rows satisfy the condition unless a second argument for empty results is provided.

Which is a valid reason to choose INDEX/MATCH over XLOOKUP in a shared workbook?

Answer: Compatibility with older tools that don't support XLOOKUP

INDEX/MATCH is broadly supported across older spreadsheet versions and export formats where XLOOKUP may not exist.

In a VLOOKUP referencing another sheet, how is the range typically written?

Answer: 'Sheet Name'!A2:C10

Cross-sheet references use the sheet name (quoted if it has spaces) followed by an exclamation mark and the range.

What does wrapping a MATCH result inside INDEX protect you from compared to raw VLOOKUP?

Answer: Broken references when columns are inserted

INDEX/MATCH references columns by position dynamically, so inserting columns doesn't misalign the return like VLOOKUP's fixed index does.