Formulas & Functions Quiz

Test your knowledge of Excel's most useful functions.

1. Which function should you use to look up a value and return a result from another column?

XLOOKUP SUM IF COUNTIF

XLOOKUP searches a range and returns a corresponding value from another range — the modern go-to for lookups.

2. What does the SUMIF function require at minimum?

A range and criteria Only a range Only criteria A PivotTable

SUMIF needs at least a range to check and criteria to match against; the sum range is optional and defaults to the checked range.

3. Which function combines multiple conditions and returns TRUE only if all are true?

AND OR IF XOR

AND returns TRUE only when every condition passed to it is true.

4. What does IFERROR do?

Returns a fallback value if a formula errors Deletes cells with errors Highlights errors in red Converts errors into zero automatically

IFERROR returns a fallback value when a formula results in an error, and the formula's normal result otherwise.

5. Which pair of functions together can look up a value from any column, not just the leftmost one?

INDEX and MATCH SUM and IF VLOOKUP and HLOOKUP alone COUNTIF and SUMIF

INDEX and MATCH together can look up a value based on a condition in any column, unlike VLOOKUP which only searches the leftmost column of its range.

Learn Microsoft Excel step by step — lessons, formulas, shortcuts, and real-world projects.