0
functions, fully built
0
sourced error fixes
0
platform checks
0k
rows benchmarked
// the cluster
Twenty-four functions, one method.
The lookup family, the logical core (IF / IFS / SWITCH / IFNA and the AND / OR / NOT / XOR gate with live truth tables), the statistical family (SUMIF / SUMIFS / COUNTIF / COUNTIFS / AVERAGEIF criteria galleries) and the text family (LEFT / RIGHT / MID / LEN position galleries). Each page is a self-contained lab, not a reworded reference.
HLOOKUP→
Master HLOOKUP: syntax, row-oriented lookups, the row_index_num trap, 14 sourced error fixes, an 8-platform compatibil
IFERROR→
Master IFERROR with a live wrap toggle, an interactive lookup playground, and a sourced error library: tame #N/A witho
INDEX-MATCH→
Master INDEX-MATCH: syntax, left lookups, two-way matrix lookups, 14 sourced error fixes, an 8-platform compatibility
VLOOKUP→
Master VLOOKUP with live examples, an interactive lookup playground, and original performance benchmarks: VLOOKUP vs I
XLOOKUP→
The XLOOKUP function measured, not just described: a reproducible 100,000-row benchmark against VLOOKUP and INDEX-MATC
IF→
Master IF: the decision gallery, the short-circuit proof, IF vs IFS, 14 sourced error fixes, an 8-platform compatibility matrix and a live decision playground.
IFS→
Master IFS: the first-TRUE-wins order gallery, the #N/A default trap and its TRUE() fix, IFS vs IF vs SWITCH, 10 sourced error fixes, an 8-platform compatibility matrix and a live grading playground.
SWITCH→
Master SWITCH: the exact-match first-match-wins gallery, the #N/A default trap, case-insensitive and text-vs-number gotchas, SWITCH vs IFS vs IF, 10 sourced error fixes, an 8-platform compatibility matrix and a live status-code playground.
IFNA→
Master IFNA: the measured error-catch matrix (IFNA vs IFERROR vs ISNA/ISERR), the #N/A-only rule, the 0-vs-blank fallback trap, IFNA vs IFERROR, 12 sourced error fixes, an 8-platform compatibility matrix and a live lookup playground.
AND→
Master AND: the all-must-pass gate, a clickable live truth table, measured number/text coercion, the eager-evaluation trap and the chained-comparison fix, 8 sourced error fixes, an 8-platform compatibility matrix and a live boolean playground.
OR→
Master OR: the any-passes trigger, a clickable live truth table, measured coercion, the eager-evaluation trap and OR-vs-AND choice, 8 sourced error fixes, an 8-platform compatibility matrix and a live boolean playground.
NOT→
Master NOT: logical inversion, a clickable live truth table, De Morgan’s laws measured, guard patterns like NOT(ISBLANK()) and double negation, 8 sourced error fixes, an 8-platform compatibility matrix and a live boolean playground.
XOR→
Master XOR: odd-parity, not “exactly one” — a clickable live truth table, the 3- and 4-input parity surprise, the eager-evaluation trap, 8 sourced error fixes, a 9-platform compatibility matrix and a live boolean playground.
SUMIF→
Master SUMIF: a measured criteria gallery (exact, wildcard, comparison, not-equal), the range/sum_range alignment trap, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
SUMIFS→
Master SUMIFS: a multi-condition gallery where every condition must pass, the sum_range-first argument flip vs SUMIF, AND-not-OR logic, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
COUNTIF→
Master COUNTIF: a measured criteria gallery that counts matching rows, the text-vs-number and wildcard traps, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
COUNTIFS→
Master COUNTIFS: a multi-condition gallery counting rows not values, AND-ed criteria and equal-range sizing, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
AVERAGEIF→
Master AVERAGEIF: a criteria gallery where no match is #DIV/0! not 0, the average_range alignment trap, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
LEFT→
Master LEFT: a position gallery where num_chars defaults to 1, the negative-#VALUE! and over-length traps, 8 sourced error fixes, an 8-platform compatibility matrix and a live text playground.
RIGHT→
Master RIGHT: a position gallery counting back from the end, the mirror-of-LEFT logic and negative-#VALUE! trap, 8 sourced error fixes, an 8-platform compatibility matrix and a live text playground.
MID→
Master MID: a position gallery where start_num is 1-based, the start-0 #VALUE! and past-end empty traps, 8 sourced error fixes, an 8-platform compatibility matrix and a live text playground.
LEN→
Master LEN: a length gallery counting every character including spaces, the bytes-vs-characters and number-coercion traps, 8 sourced error fixes, an 8-platform compatibility matrix and a live playground.
TRUE→
Master TRUE: a measured behavior gallery for the boolean constant, TRUE() vs the bare word and coercion rules, 6 sourced error fixes, an 8-platform compatibility matrix and a live boolean playground.
FALSE→
Master FALSE: a measured behavior gallery for the boolean constant, FALSE() vs 0 vs empty and coercion rules, 6 sourced error fixes, an 8-platform compatibility matrix and a live boolean playground.
// why trust it
We measure what others assert.
01
Reproducible benchmark
HyperFormula 3.3.1, exact-match full-column references, median of repeated runs, correctness checked against a known sum. The raw JSON ships with every page.
02
Sourced error library
Real failure modes from Microsoft, Exceljet, Ablebits and r/excel — each with symptom, root cause, a runnable fix and a citation. Not invented examples.
03
Live, steerable playground
Edit the cells, change the key, switch the function, add rows. The formula and result recompute instantly — including a transposed grid for HLOOKUP.
100,000 rows · 1,000 lookups
median of 3 runs · lower is faster
VLOOKUP10.87s
XLOOKUP15.05s
INDEX-MATCH18.03s
VLOOKUP wins the exact-match full-column case in our engine. HLOOKUP shares its lookup class. Full methodology and per-size tables on each function page.
// in the pipeline
The cluster keeps growing.
Next functions to get the same measured treatment. The lookup family links to these as they ship.