Your first Power Query pipeline: from monthly clean-up to one click
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.
VLOOKUP is the most widely known function in Excel and, measured by the errors it causes, one of the most dangerous. Not because it is badly designed, but because it makes three assumptions that quietly stop being true as a workbook evolves.
Write =VLOOKUP(A2, Data!A:F, 4, FALSE) and you have hard-coded the fact that the value you want is in the fourth column. The day somebody inserts a column into the source — an entirely reasonable thing to do — the formula keeps working and silently returns the wrong field. No error, no warning, just a different number.
XLOOKUP takes a lookup array and a return array as separate references. There is no index number to become wrong: =XLOOKUP(A2, Data[ID], Data[Price]) still returns the price whatever happens to the columns around it.
VLOOKUP requires the lookup column to be the leftmost of the range. When the field you want sits to the left of the key, the usual workarounds are to duplicate a column or fall back to INDEX/MATCH. Both are extra structure that has to be maintained.
XLOOKUP does not care about direction. The lookup array and the return array can be anywhere, in any order, in any sheet.
Omit the fourth argument in VLOOKUP and it performs an approximate match, which on unsorted data returns results that look plausible and are wrong. Most people learn to always type FALSE, and most people occasionally forget.
XLOOKUP defaults to an exact match. The dangerous behaviour is opt-in rather than opt-out, which is the correct way round.
A built-in "if not found" argument. Instead of wrapping the whole formula in IFERROR — which also hides genuine errors like a broken reference — you write =XLOOKUP(A2, IDs, Prices, "Not found"). Only the not-found case is handled; a #REF! still shows up as a #REF!, which is exactly what you want.
Searching from the end. The sixth argument lets you search bottom-up, which makes "find the most recent transaction for this customer" a single formula rather than an array construction.
XLOOKUP requires Microsoft 365 or Excel 2021. On Excel 2019 or earlier the correct alternative is INDEX/MATCH, not VLOOKUP:
=INDEX(Data[Price], MATCH(A2, Data[ID], 0))
It solves the same three problems: no hard-coded column index, no directional constraint, and the 0 forces an exact match explicitly. It is slightly harder to read, which is the only reason XLOOKUP replaced it.
No. Rewriting working formulas for their own sake introduces risk without adding value. The sensible rule is: write new lookups with XLOOKUP, and replace an existing VLOOKUP when you are already editing that part of the workbook, or when it has already caused a problem. Untouched formulas that are correct can stay correct.
One exception is worth making time for: any VLOOKUP with a hard-coded column index pointing into a source you do not control — an export from another system, or a file another team maintains. Those are the ones that break at the worst possible moment.
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.
Most dashboards fail for design reasons, not technical ones. These six account for the majority of the ones nobody opens twice.