Spreadsheet function comparisons

Head-to-head guides for the functions people mix up — what actually differs, which to use when, and how support varies across Excel, Google Sheets, and LibreOffice (backed by executed tests, not documentation).

• CONCATENATE vs CONCAT vs TEXTJOIN: which to use?

  Three generations of the same idea: CONCATENATE is the legacy function, CONCAT adds range support, and TEXTJOIN adds the two things everyone actually wants...

• FILTER vs QUERY: Sheets' two ways to slice data

  In Google Sheets these overlap heavily, so people ask which to learn. The portability answer is decisive: FILTER now exists in Excel and LibreOffice too...

• IF vs IFS: when to stop nesting

  IFS exists to kill the nested-IF pyramid: =IF(a,x,IF(b,y,IF(c,z,...))) becomes one flat list of condition/value pairs. The catch: IFS has no built-in...

• SUMIF vs SUMIFS: what's the difference?

  SUMIFS does everything SUMIF does and handles multiple conditions — but the two put their arguments in OPPOSITE orders, which is the bug most people...

• SUMIF vs SUMPRODUCT: conditional sums two ways

  SUMIF (and SUMIFS) cover the common cases fast; SUMPRODUCT is the escape hatch when conditions get interesting — OR logic, calculations inside the...

• VLOOKUP vs INDEX/MATCH: which is better?

  INDEX/MATCH was the power user's lookup long before XLOOKUP existed, and it still matters because it runs in every spreadsheet app and every version — while...

• VLOOKUP vs XLOOKUP: which should you use?

  XLOOKUP is VLOOKUP's modern replacement — but VLOOKUP still wins on one thing: it runs everywhere, including old Excel versions and every LibreOffice...

• XLOOKUP vs INDEX/MATCH: is there still a reason to nest?

  XLOOKUP does in one call nearly everything INDEX/MATCH does in two — so the real question is portability: INDEX/MATCH works in every spreadsheet ever...