XLOOKUP vs VLOOKUP: what actually changes in your work
VLOOKUP has three structural weaknesses that cause most lookup errors. XLOOKUP removes all three — here is exactly how, and what to do when your Excel version does not have it.
Most spreadsheet risk is not caused by a wrong formula. It is caused by a file that only one person understands, running something the business depends on. When that person is unavailable, the organisation discovers how much risk it was carrying.
Structure fixes this, and it costs almost nothing if you do it from the start.
Three kinds of content, three separate places:
The rule that follows is simple and powerful: no hard-coded number inside a formula in the calculation layer. If a VAT rate of 0.2 appears inside a formula, it belongs in the inputs sheet with a label. When the rate changes, one cell changes rather than a search across forty tabs.
The financial modelling convention is worth adopting because it is widely understood: blue text for hard-coded inputs, black for formulas, green for references to other sheets. A reviewer can then see at a glance where the assumptions are. Whatever convention you pick, document it on the cover sheet and apply it consistently.
The first tab, before anything else. Five things: what this workbook does, who owns it, where the source data comes from, how to update it for a new period, and the date of the last significant change. Ten minutes of writing that saves hours for whoever inherits it — including you, eighteen months from now.
Convert data ranges to Excel Tables (Ctrl+T). Structured references such as Sales[Amount] are readable, and they expand automatically when rows are added — which removes an entire category of "the formula did not include the new rows" errors.
Name the key inputs. =Revenue*VAT_Rate is self-documenting; =B12*$F$3 requires a trip across the workbook to understand.
A 300-character formula is not clever; it is unmaintainable. Split it into helper columns with clear headers, or use LET to name intermediate steps. Both make the logic reviewable. The performance difference is irrelevant at any size a human works with, and reviewability is worth far more.
Add a small validation block: does the total of the detail equal the summary figure? Does the balance reconcile? A single cell that reads OK or CHECK, formatted conditionally, catches the class of error that is otherwise found by a stakeholder in a meeting.
Before calling a workbook finished, ask: could a competent colleague open this, work out what it does from the cover sheet, update it for next month, and change an assumption — without calling me? If the honest answer is no, the file is not finished. It is just working.
VLOOKUP has three structural weaknesses that cause most lookup errors. XLOOKUP removes all three — here is exactly how, and what to do when your Excel version does not have it.
The single highest-return change most Excel users can make. A worked example, the mistakes that make queries fragile, and how to build one that survives next month.
Sales says 4,120 active customers. Finance says 3,880. Both are right. The problem is not the data — it is that nobody wrote down what "active" means.