pbPassingBI
/
excel/flashcards/excel-cards-formulas

Formulas & lookup functions — flashcards

One card per lesson — 6 in this module. Recall the key points, then check. 6 cards. Click a card to flip, or use the arrow keys and space bar.

  1. IF, IFS and logical tests — what are the key points?

    IFS reads better than nested IF; conditions test in order · TRUE/FALSE act as 1/0 in arithmetic — the basis of SUMPRODUCT counting · Prefer IFNA over IFERROR with lookups so real errors stay visible Read the lesson

  2. XLOOKUP, VLOOKUP and INDEX/MATCH — what are the key points?

    VLOOKUP looks right only and breaks on column insertion · XLOOKUP defaults to exact match and has built-in not-found handling · INDEX/MATCH remains the portable answer for older Excel Read the lesson

  3. Dynamic arrays and spilling — what are the key points?

    One formula spills into many cells; #SPILL! means the area is blocked · UNIQUE, SORT, FILTER and SEQUENCE compose together · The # operator references a whole spill range dynamically Read the lesson

  4. Text functions — what are the key points?

    TEXTJOIN combines a range with a delimiter and skips blanks · TRIM both sides before any lookup — whitespace is invisible · TEXTBEFORE and TEXTAFTER replace most LEFT/FIND combinations Read the lesson

  5. Date functions — what are the key points?

    DATEDIF is undocumented but is the correct age calculation · NETWORKDAYS excludes weekends and an optional holiday list · Group by a real date or a zero-padded string, not a month name Read the lesson

  6. SUMIFS, COUNTIFS and AVERAGEIFS — what are the key points?

    SUMIFS takes the sum range first; SUMIF takes it last · Comparison criteria are text — use "&" to reference a cell · Mixed references let one SUMIFS fill an entire grid Read the lesson