Advanced Excel Functions & Formulas Flashcards

When the match_mode argument of XLOOKUP is omitted, which type of match does Excel perform?

Answer: Exact match

XLOOKUP defaults to match_mode 0, an exact match, unlike VLOOKUP which defaults to approximate.

What does =INDEX(B2:B10, MATCH("Smith", A2:A10, 0)) return?

Answer: The value in B2:B10 on the same row as the first exact match of "Smith" in A2:A10

MATCH returns the position of the first exact match and INDEX returns the value at that position in column B.

If the range_lookup argument of VLOOKUP is omitted, what must be true for reliable results?

Answer: The first column of the table must be sorted in ascending order

Omitting range_lookup defaults to TRUE (approximate match), which requires the first column to be sorted ascending.

Which XLOOKUP search_mode returns the most recent (last) occurrence of a value in a list sorted by date ascending?

Answer: -1

search_mode -1 searches from last to first, returning the last matching entry.

MATCH with match_type -1 requires the lookup array to be sorted how?

Answer: Descending order

match_type -1 finds the smallest value greater than or equal to the lookup value and needs a descending sort.

How does IFNA differ from IFERROR when wrapping a lookup formula?

Answer: IFNA traps only #N/A errors, while IFERROR traps all error types

IFNA handles only #N/A, so other errors like #REF! or #VALUE! still surface for troubleshooting.

In =XLOOKUP(E2, A:A, B:B, "Not found"), what is the role of "Not found"?

Answer: It is the if_not_found value returned when no match exists

The fourth XLOOKUP argument, if_not_found, replaces the #N/A result when no match is found.