The argument order that breaks spreadsheets
SUMIF takes the range you are testing first and the range you are adding last. SUMIFS takes the range you are adding first. AVERAGEIFS, COUNTIFS and MAXIFS follow the SUMIFS pattern. There is no reason for the inconsistency beyond the order in which Microsoft shipped them, and it produces a particular kind of bug: the formula returns a number, the number is wrong, and nothing about it looks wrong.
The practical answer is to stop using SUMIF. SUMIFS handles a single condition perfectly well, the argument order is consistent with everything around it, and you gain the ability to add a second condition later without rewriting. The same applies to COUNTIF against COUNTIFS.
VLOOKUP, and why its two defaults are both wrong
VLOOKUP has a fourth argument that decides between exact and approximate matching, and it is optional. Leave it out and you get approximate. On a sorted numeric table that is a legitimate mode -- it is how tax brackets and grade boundaries work. On an unsorted list of names or codes, which is what most people are looking up, it returns whatever it stumbled across, with no error to tell you. Always type FALSE or 0.
The second problem is the column index. It counts from the left edge of the table you handed in, so =VLOOKUP(A2,Data!A:F,4,FALSE) means the fourth column of that block. Insert a column into the source data and the formula still calculates, still returns a value, and now returns the wrong one. INDEX and MATCH does not have this failure mode because it refers to the return column directly. Neither does XLOOKUP.
| VLOOKUP | INDEX + MATCH | XLOOKUP | |
|---|---|---|---|
| Default match | Approximate | Approximate unless you write 0 | Exact |
| Survives an inserted column | No | Yes | Yes |
| Can look leftwards | No | Yes | Yes |
| Built-in not-found value | No | No | Yes |
| Opens in Excel 2019 | Yes | Yes | No |
That last row decides it in practice. If the file has to be opened by someone on an older version, XLOOKUP will show as _xlfn.XLOOKUP and a #NAME? error, and the sheet is useless to them. INDEX and MATCH is the portable answer.
Where dynamic arrays changed things
UNIQUE, FILTER, SORT and SEQUENCE arrived in Excel 2021 and Microsoft 365, and they replace a large amount of older technique. A formula that returns many values now spills into the cells below it automatically, so the Ctrl+Shift+Enter ritual is gone, Remove Duplicates as a one-off command has a live alternative, and returning every match for a lookup value -- the thing people were always trying to force VLOOKUP to do -- is a single FILTER.
Two cautions. A spill range needs empty cells to spill into; anything in the way produces #SPILL! rather than overwriting. And the compatibility cliff is real: none of these functions exist in Excel 2019 or earlier, and a workbook that uses them cannot be shared with someone on an older build in any useful state.
Google Sheets is close but not identical
Most of the functions here behave the same in Sheets, and the compatibility column says which do not. The differences that actually bite are date formatting through TEXT, where the format codes differ; TEXTSPLIT, which Sheets calls SPLIT with different arguments; and the fact that Sheets has QUERY, a small SQL-like language for grouping and aggregating, with no Excel equivalent at all. Going the other way, AGGREGATE and several newer array-shaping functions are Excel only.
The other practical difference is scale. Sheets slows down on a few tens of thousands of rows with volatile formulas in a way Excel does not, so OFFSET, INDIRECT, NOW and RAND are more costly there.
Making a formula you can still read next year
Three habits do most of the work. Wrap the smallest expression you can in IFNA rather than putting IFERROR around the whole formula, because IFERROR will also swallow the #REF! that tells you someone deleted a column. Use LET to name intermediate results, so a long formula computes a lookup once and reads like a sentence. And put lookup tables in a named range or an Excel Table, so the formula says Prices rather than Sheet3!$A$2:$D$5000 and stops needing maintenance every time the data grows.
Questions people ask
Should I use XLOOKUP or INDEX MATCH?
XLOOKUP if everyone who opens the file is on Excel 2021 or Microsoft 365 -- it is shorter, exact by default, and has a not-found argument built in. INDEX MATCH if the file goes anywhere else, because XLOOKUP does not exist in Excel 2019 and earlier and the formula will show a #NAME? error there rather than degrading gracefully.
My VLOOKUP returns #N/A but I can see the value in the table.
Almost always a type or whitespace mismatch. The lookup value is text "1001" and the table holds the number 1001, or one side has a trailing space from an export. Compare LEN of both cells to catch the space, and use VALUE or TEXT to bring the types into line. A non-breaking space from a web paste survives TRIM, so substitute CHAR(160) first.
What is the difference between COUNT and COUNTA?
COUNT counts only numbers; COUNTA counts anything that is not an empty cell. Running both over the same column is the quickest test for numbers that have been imported as text -- if COUNTA is higher than COUNT, some of those values are text and will not sum. Note that COUNTA also counts a cell holding "" returned by a formula, which looks empty on screen.
Why does my percentage show 15% when I typed 0.15?
Percent formatting multiplies the display by 100 without changing the stored value. The cell holds 0.15 and shows 15%. If you type 15 into a cell already formatted as percent, Excel stores 0.15 in some versions and 15 in others depending on the entry mode, which is why a percent column is worth checking with Ctrl+Shift+~ to see the raw numbers.
Is DATEDIF safe to use? It is not in the function list.
It works in every version of Excel and in Google Sheets, and it has done for decades, but Microsoft does not document it and the editor will not autocomplete it. It exists for Lotus 1-2-3 compatibility. For whole-year age or tenure it is the correct tool, because subtracting dates and dividing by 365.25 gets the leap years wrong at the boundaries.