pbPassingBI
/
excel/quizzes/excel-quiz-formulas
12 questions

Formulas & lookup functions — quiz

12 questions covering this module. Logical, text, date, lookup and conditional aggregation functions.

  1. Which is safer around a lookup formula?

    1. IFERROR
    2. IFNA
    3. ISERROR
    4. IFS

    Answer: IFNA — IFNA catches only #N/A, leaving genuine errors like #REF! visible.

  2. In IFS, what does a final TRUE condition do?

    1. Errors out
    2. Acts as the catch-all else branch
    3. Repeats the first test
    4. Is ignored

    Answer: Acts as the catch-all else branch — Conditions evaluate in order; a trailing TRUE always matches, giving you an else.

  3. Why does VLOOKUP break when someone inserts a column?

    1. It caches results
    2. col_index_num is a hard-coded position
    3. It requires sorted data
    4. It only works in Tables

    Answer: col_index_num is a hard-coded position — The column index is a number, so inserting a column shifts what that number points at.

  4. Which XLOOKUP argument finds the most recent matching record?

    1. match_mode = -1
    2. search_mode = -1
    3. if_not_found
    4. match_mode = 2

    Answer: search_mode = -1 — search_mode = -1 searches from the bottom up, returning the last match first.

  5. What does #SPILL! mean?

    1. The formula is wrong
    2. Something is blocking the spill range
    3. The file is too large
    4. Circular reference

    Answer: Something is blocking the spill range — The formula is valid but cannot write its results because cells in the target area are occupied.

  6. E2# refers to:

    1. Cell E2 only
    2. The entire spill range beginning at E2
    3. Column E
    4. An error value

    Answer: The entire spill range beginning at E2 — The # operator captures the full dynamic spill range, however it resizes.

  7. XLOOKUP returns #N/A on values that look identical. First check:

    1. The lookup range
    2. Trailing whitespace — wrap both in TRIM
    3. The match mode
    4. Data types

    Answer: Trailing whitespace — wrap both in TRIM — Whitespace is invisible in the cell.

  8. Which joins a range with a delimiter and skips blanks?

    1. CONCAT
    2. TEXTJOIN
    3. SUBSTITUTE
    4. TEXTSPLIT

    Answer: TEXTJOIN — The second argument controls whether empties are ignored.

  9. Which correctly calculates complete years between two dates?

    1. (B2-A2)/365
    2. DATEDIF(A2,B2,"y")
    3. YEAR(B2)-YEAR(A2)
    4. DAYS(B2,A2)

    Answer: DATEDIF(A2,B2,"y") — Subtraction and division mishandles leap years.

  10. Grouping by =TEXT(date,"mmm") sorts:

    1. Chronologically
    2. Alphabetically
    3. By value
    4. Randomly

    Answer: Alphabetically — Apr before Jan. Use a zero-padded year-month instead.

  11. SUMIFS differs from SUMIF in that:

    1. It is faster
    2. The sum range comes first rather than last
    3. It allows wildcards
    4. It handles dates

    Answer: The sum range comes first rather than last — Mixing them gives a wrong number, not an error.

  12. To compare against a value in B1, write the criteria as:

    1. ">B1"
    2. ">"&B1
    3. B1
    4. ">=B1"

    Answer: ">"&B1 — The ampersand concatenates the operator with the cell value.