Excel function comparisons
Functions that look alike but behave differently. Each comparison runs both on the same sample sheet, so you can see the difference, then practice it.
VLOOKUP vs XLOOKUP
Use XLOOKUP if everyone who opens the file has Excel 2021, 2024 or Microsoft 365.
XLOOKUP vs INDEX and MATCH
They do the same job. XLOOKUP is one function with readable arguments; INDEX and MATCH is a two-function pattern that works in every version of Excel.
COUNTIF vs COUNTIFS
COUNTIF counts cells that meet one condition. COUNTIFS counts rows that meet several conditions at once.
SUMIF vs SUMIFS
SUMIF adds the values that meet one condition; SUMIFS handles several.
FIND vs SEARCH
Both return the position of text inside other text. FIND is case-sensitive and takes the text literally; SEARCH ignores case.
IFERROR vs IFNA
Both replace an error with something friendlier. IFNA only catches #N/A, the "not found" error from lookups; IFERROR catches every error, including ones that point to real mistakes.
CONCAT vs TEXTJOIN vs &
All three join text. The & operator is fine for two or three pieces.
ROUND vs ROUNDUP vs ROUNDDOWN
ROUND goes to the nearest value. ROUNDUP always moves away from zero and ROUNDDOWN always moves toward it, whatever the next digit is.