pbPassingBI
/
excel/flashcards/excel-flashcards-formulas

Formulas & lookups

References, logic, lookup functions. 8 cards. Click a card to flip, or use the arrow keys and space bar.

  1. What does $ do in a reference?

    Locks whatever follows it. $A1 locks the column; A$1 locks the row; $A$1 locks both.

  2. Key advantage of XLOOKUP over VLOOKUP?

    Exact match by default, searches any direction, built-in not-found argument, and does not break when columns are inserted.

  3. Why does VLOOKUP break on column insertion?

    col_index_num is a hard-coded position, so inserting a column shifts what it points at.

  4. IFERROR vs IFNA?

    IFERROR catches every error; IFNA catches only #N/A. Prefer IFNA with lookups so genuine errors stay visible.

  5. What does #SPILL! mean?

    A dynamic array formula cannot write its results because cells in the spill range are occupied.

  6. What does E2# reference?

    The entire spill range beginning at E2, resizing automatically.

  7. What does LET do?

    Names intermediate values inside a formula, improving readability and avoiding repeat calculation.

  8. #REF! means what?

    A referenced cell no longer exists — usually it was deleted.