The lookup family has been redesigned around XLOOKUP, but understanding the legacy functions remains important. `=VLOOKUP(value, table, col, FALSE)` looks up by the leftmost column of a range and returns a column to its right — it cannot look left. `=XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])` removes those restrictions: it can look in any direction, defaults to exact match, and accepts a built-in "not found" fallback; passing `match_mode = 2` enables wildcard matching (`*`, `?`). For complex data with row and column orientation, the `=INDEX(returnRange, MATCH(lookupValue, lookupRange, 0))` pattern is more robust than VLOOKUP, and INDEX/MATCH scales better than OFFSET — which is volatile and forces recalculation on every change. Multi-criteria lookups in dynamic-array Excel collapse to `=XLOOKUP(1, (A:A=x)*(B:B=y), C:C)`; the boolean product acts as an AND across the criteria arrays. To fetch only the last non-blank value in a column, the classic trick `=LOOKUP(2, 1/(A:A<>""), A:A)` works without array-entering.
Modern Excel replaces Ctrl-Shift-Enter array formulas with dynamic spilling. `=SORT(UNIQUE(A1:A100))` produces a clean sorted list in one motion, and `=FILTER(array, include, [if_empty])`, `=SORT(array, [sort_index], [order])`, and `=SORTBY(array, by_array, [order])` round out the toolkit. `=VSTACK(range1, range2)` and `=HSTACK(range1, range2)` stack ranges vertically or horizontally — useful for stitching monthly extracts into one master table. `=LET(x, A1*2, y, x+5, y*3)` names intermediate results so the same sub-expression isn't recomputed; `=LAMBDA(x, x*2)(5)` defines a reusable function inline, and named LAMBDAs in Name Manager replace the old VBA custom-function workflow. When a spill is blocked, `#SPILL!` appears; clearing the blocking cells or wrapping with `@` for implicit intersection resolves the conflict. To reference the entire spill range of another cell, use the spill range operator `=A2#`, which is cleaner and far cheaper than OFFSET.
Several smaller utilities round out the lookup toolkit. `=IFS(cond1, val1, cond2, val2, TRUE, "default")` reads better than deeply nested IFs, and `=SWITCH(value, 1, "Low", 2, "Med", 3, "High", "Other")` is the right tool when one expression is compared to a list of candidates. References are anchored with `$A\(1` (or selectively with `\)A1` or `A$1`), cycled by pressing F4 while the cursor is in the reference. For ad-hoc multi-criteria lookups without SUMIFS, the database functions `=DGET(database, field, criteria)` and `=DSUM(database, field, criteria_block)` work with a header-row criteria range. `=IFNA(...)` traps only `#N/A` so genuine bugs surface, whereas `=IFERROR(...)` catches everything and can mask real mistakes — pick the narrower trap when possible. To leap to a specific cell from any sheet, `=HYPERLINK("#'P&L'!A1", "Open P&L")` keeps a navigation menu at the top of the model. Volatile functions (`OFFSET`, `INDIRECT`, `NOW`, `TODAY`, `RAND`, `RANDBETWEEN`) recalc on every change and slow large workbooks; prefer `INDEX`-based dynamic ranges wherever feasible.