Microsoft Excel Formulas and Functions 3

Question 1

What is XLOOKUP's key advantage over VLOOKUP?

Accepted Answer

Can search left, defaults to exact match, no column index needed

Answer

XLOOKUP uses separate arrays (no column index), searches any direction, defaults exact match, has built-in error handling.

Question 2

How does the CHOOSE function work?

Accepted Answer

Returns a value from a list based on index number

Answer

CHOOSE returns the value at position index_num from up to 254 values.

Question 3

What does TRIM do and when is it essential?

Accepted Answer

Removes extra spaces, leaving single spaces between words

Answer

TRIM removes leading, trailing, and extra internal spaces.

Question 4

How does OFFSET create a dynamic reference?

Accepted Answer

Returns a reference shifted from a starting point by rows, columns, and optional size

Answer

OFFSET creates a reference offset from a starting cell with optional height and width.

Question 5

What is the difference between COUNT, COUNTA, COUNTBLANK, and COUNTIF?

Accepted Answer

COUNT=numbers, COUNTA=non-empty, COUNTBLANK=empty, COUNTIF=by criteria

Answer

Each counts different things based on its suffix.

Excel Practice Test