CES Advanced Excel Functions & Formulas 2

Question 1

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

Accepted Answer

Exact match

Answer

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

Question 2

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

Accepted Answer

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

Answer

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

Question 3

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

Accepted Answer

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

Answer

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

Question 4

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

Accepted Answer

-1

Answer

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

Question 5

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

Accepted Answer

Descending order

Answer

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

CES - Certified Excel Specialists Practice Test