Spreadsheet Formulas
The formulas that do the real work: relative and absolute references, IF and IFS, SUMIFS and COUNTIFS, XLOOKUP and INDEX/MATCH, text and date functions, dynamic arrays such as FILTER and UNIQUE, and LET.
On Spreadsheets Done Right Lucía Romero's KPI workbook was split into a raw Readings sheet, an input sheet, a Calc sheet and an output sheet. This page fills the Calc sheet. Every grid below was typed into LibreOffice Calc 25.2 and shows the values Calc displays; formulas are written in Excel's English notation, with commas between arguments, which Calc accepts in an English setup. The numbers are Kestrel Energy's September 2026 sample data, so they can be compared with the SQL results elsewhere in this topic.
What a formula is
A spreadsheet formula is
an expression in a cell, starting with =, that
calculates the cell's value from constants, references to other
cells, operators and functions; the spreadsheet recalculates it
whenever one of its inputs changes. The cell shows the result, and
the formula bar shows the formula.
| A | B
1 | Item | Value
2 | kWh (M-30002) | 296.708
3 | Day price | 0.312
4 | Cost | =B2*B3
5 | Cost, rounded | =ROUND(B4,2)
6 | Divide by zero | =B2/0
7 | Typo in name | =SUMM(B2:B3)
8 | Text plus 1 | ="kWh"+1| A | B 1 | Item | Value 2 | kWh (M-30002) | 296.708 3 | Day price | 0.312 4 | Cost | 92.572896 5 | Cost, rounded | 92.57 6 | Divide by zero | #DIV/0! 7 | Typo in name | #NAME? 8 | Text plus 1 | #VALUE!
Operators follow the usual
precedence: ^ before * and /, before
+ and -, and & joins
text. ROUND changes the stored value; formatting a cell
to two decimals only changes what you see, and the hidden decimals
still count in later sums. An error value is not a failure of the
spreadsheet but a message, and it spreads: every formula that uses
an error cell shows the same error.
| Error | Means | Typical cause |
|---|---|---|
#DIV/0! |
Division by zero | An empty or zero cell in the denominator |
#NAME? |
Unknown name | A typo, a function your version does not have, or a localized function name |
#VALUE! |
Wrong type of value | Text used in a calculation |
#N/A |
Value not available | A lookup that found no match |
#REF! |
Invalid reference | A row, column or sheet the formula used was deleted |
#SPILL! |
A result range is blocked (Excel) | Cells below or right of a dynamic array formula are not empty |
LibreOffice Calc shows some errors as a code instead, such as
Err:508 for a missing bracket; the help lists what each
code means.
Relative and absolute references
Formulas are written once and copied down a column. That only works
because references move with the copy. A
relative reference such as
B4 is stored as a position relative to the formula's
own cell ("two columns left, same row"), so copying the formula one
row down turns it into B5. An
absolute reference such as
$B$1 points to a fixed cell and stays the same wherever
the formula is copied. Mixed references fix only one part:
$B4 fixes the column, B$1 the row.
Pressing F4 in Excel, or Shift+F4 in LibreOffice Calc, while the
cursor is on a reference cycles through the four forms.
Here are the three fixed-tariff meters priced at €0.27 per kWh, once with the price reference fixed and once as it looks after a relative formula was copied down:
| A | B | C | D
1 | Price | 0.27 | |
3 | meter_id | kwh | cost_right | cost_wrong
4 | M-30001 | 334.990 | =B4*$B$1 | =B4*B1
5 | M-30008 | 463.290 | =B5*$B$1 | =B5*B2
6 | M-30010 | 275.960 | =B6*$B$1 | =B6*B3
7 | Total | =SUM(B4:B6) | =SUM(C4:C6)| A | B | C | D 1 | Price | 0.27 | | 3 | meter_id | kwh | cost_right | cost_wrong 4 | M-30001 | 334.99 | 90.4473 | 90.4473 5 | M-30008 | 463.29 | 125.0883 | 0 6 | M-30010 | 275.96 | 74.5092 | #VALUE! 7 | Total | 1074.24 | 290.0448 |
The first row is right in both columns, which is exactly why the
mistake survives: someone checks the first result and copies the
formula down. Row 5 multiplies by an empty cell, which counts as 0,
and gives a plausible-looking zero. Only row 6 shows an error,
because B3 happens to hold text. Check a copied formula in the
last row, not the first. A named input cell
(=B4*FixedPrice, see
named ranges) avoids the question altogether, because a name always points to
the same place.
IF and IFS
IF(condition, value_if_true, value_if_false) chooses
between two results. Several conditions used to mean nested IFs,
which are hard to read; IFS (Excel 2019 and later,
LibreOffice, Google Sheets) takes pairs of condition and result and
returns the result of the first condition that is TRUE. A final
TRUE condition acts as "everything else". This is the
same idea as SQL's
CASE expression.
| A | B | C
1 | meter_id | kwh | size
2 | M-30006 | 1554.960 | =IFS(B2>=1000,"large",B2>=300,"medium",TRUE,"small")
3 | M-30001 | 334.990 | =IFS(B3>=1000,"large",B3>=300,"medium",TRUE,"small")
4 | M-30009 | 168.004 | =IFS(B4>=1000,"large",B4>=300,"medium",TRUE,"small")
5 | M-30009 | -4.210 | =IF(B5<0,"check reading","ok")| A | B | C 1 | meter_id | kwh | size 2 | M-30006 | 1554.96 | large 3 | M-30001 | 334.99 | medium 4 | M-30009 | 168.004 | small 5 | M-30009 | -4.21 | check reading
The order of the conditions matters: test the largest threshold
first, or every large meter is labeled "medium". Without the final
TRUE pair, a value that matches no condition gives
#N/A. Row 5 uses a plain IF as a check column for the
negative reading of meter M-30009 on 21 September, one of the
planted problems from
Data Types and Data Quality.
SUMIFS and COUNTIFS
SUMIFS adds up the cells of one range
for the rows where every condition holds:
SUMIFS(sum_range, criteria_range1, criteria1, …). Its
partner COUNTIFS counts those rows, and
AVERAGEIFS, MAXIFS and
MINIFS work the same way. They are the workhorses of a
KPI sheet. With September's import per meter on one sheet, kWh per
tariff is one formula, copied down:
| A | B | C | D | E | F
1 | meter_id | tariff | kwh | | tariff | kwh
2 | M-30001 | fixed | 334.990 | | day_night | =SUMIFS(C$2:C$13,B$2:B$13,E2)
3 | M-30002 | day_night | 296.708 | | dynamic | =SUMIFS(C$2:C$13,B$2:B$13,E3)
4 | M-30004 | dynamic | 271.212 | | fixed | =SUMIFS(C$2:C$13,B$2:B$13,E4)
5 | M-30005 | variable | 208.233 | | variable | =SUMIFS(C$2:C$13,B$2:B$13,E5)
6 | M-30006 | dynamic | 1554.960 | | Total | =SUM(F2:F5)
7 | M-30007 | day_night | 336.270 | | Meters >= 300 | =COUNTIFS(C$2:C$13,">=300")
8 | M-30008 | fixed | 463.290 | | dynamic, >= 300 | =COUNTIFS(B$2:B$13,"dynamic",C$2:C$13,">=300")
9 | M-30009 | variable | 168.004
10 | M-30010 | fixed | 275.960
11 | M-30011 | day_night | 1195.540
12 | M-30012 | dynamic | 231.104
13 | M-30014 | variable | 221.324| A | B | C | E | F 1 | meter_id | tariff | kwh | tariff | kwh 2 | M-30001 | fixed | 334.99 | day_night | 1828.518 3 | M-30002 | day_night | 296.708 | dynamic | 2057.276 4 | M-30004 | dynamic | 271.212 | fixed | 1074.24 5 | M-30005 | variable | 208.233 | variable | 597.561 6 | M-30006 | dynamic | 1554.96 | Total | 5557.595 7 | M-30007 | day_night | 336.27 | Meters >= 300 | 5 8 | M-30008 | fixed | 463.29 | dynamic, >= 300 | 1 9 | M-30009 | variable | 168.004 | | 10 | M-30010 | fixed | 275.96 | | 11 | M-30011 | day_night | 1195.54 | | 12 | M-30012 | dynamic | 231.104 | | 13 | M-30014 | variable | 221.324 | |
A few details make this formula robust. The ranges use
$ on the row numbers (C$2:C$13) so they
stay put when the formula is copied down, while the criterion
E2 moves along. All ranges must have the same size. A
criterion can be a value, a cell, or a comparison written as text
(">=300", or ">="&H1 to compare
with a cell). Text criteria ignore case, as the
previous page
showed. In an Excel table (say MeterTotals, with
columns meter_id, tariff and kwh) the formula reads
=SUMIFS(MeterTotals[kwh], MeterTotals[tariff], E2) and
grows with the data.
The same totals in SQL are one GROUP BY. Run on the full daily readings in DuckDB, they match to the last decimal, and the "Total" check cell above matches the sum of all readings:
SELECT c.tariff, sum(r.kwh_import) AS kwh
FROM daily_readings r
JOIN meters m USING (meter_id)
JOIN contracts c USING (contract_id)
GROUP BY c.tariff
ORDER BY c.tariff;┌───────────┬───────────────┐ │ tariff │ kwh │ │ varchar │ decimal(38,3) │ ├───────────┼───────────────┤ │ day_night │ 1828.518 │ │ dynamic │ 2057.276 │ │ fixed │ 1074.240 │ │ variable │ 597.561 │ └───────────┴───────────────┘
Both still contain the raw data's problems (the duplicate reading, the reading ten times too high, the meter whose customer does not exist). A formula sums what it is given; whether the inputs are right is the question of Data Types and Data Quality.
Lookups
A lookup fetches a value from another table by matching a key: the customer's name for a contract, the tariff for a meter, the price for a date. It is the spreadsheet version of a left join, one cell at a time.
XLOOKUP
XLOOKUP searches one range for a
value and returns the item in the same position of another range:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode]). It matches exactly by default, can look left as well as right,
and takes a value to show when nothing is found. It is available in
Excel 2021, 2024 and Microsoft 365, in Google Sheets, and in
LibreOffice from version 24.8; not in Excel 2019 or 2016.
| A | B | C | D | E | F
1 | contract_id | customer_id | name | | customer_id | name
2 | CT-20004 | C-10003 | =XLOOKUP(B2,E$2:E$4,F$2:F$4,"unknown customer") | | C-10003 | Lotte Kortenhoeve
3 | CT-20006 | C-10005 | =XLOOKUP(B3,E$2:E$4,F$2:F$4,"unknown customer") | | C-10005 | Bakkerij Korenschoof B.V.
4 | CT-20014 | C-10099 | =XLOOKUP(B4,E$2:E$4,F$2:F$4,"unknown customer") | | C-10011 | Marit Wolterbeek
5 | CT-20014 | C-10099 | =XLOOKUP(B5,E$2:E$4,F$2:F$4)
6 | CT-20006 | C-10005 | =INDEX(F$2:F$4,MATCH(B6,E$2:E$4,0))
7 | CT-20006 | C-10005 | =VLOOKUP(B7,E$2:F$4,2,FALSE)| A | B | C | E | F 1 | contract_id | customer_id | name | customer_id | name 2 | CT-20004 | C-10003 | Lotte Kortenhoeve | C-10003 | Lotte Kortenhoeve 3 | CT-20006 | C-10005 | Bakkerij Korenschoof B.V. | C-10005 | Bakkerij Korenschoof B.V. 4 | CT-20014 | C-10099 | unknown customer | C-10011 | Marit Wolterbeek 5 | CT-20014 | C-10099 | #N/A | | 6 | CT-20006 | C-10005 | Bakkerij Korenschoof B.V. | | 7 | CT-20006 | C-10005 | Bakkerij Korenschoof B.V. | |
Contract CT-20014 belongs to customer C-10099, who does not exist:
the orphan contract from the planted data. Without the fourth
argument the lookup returns #N/A (row 5), which at
least shows up. Be careful with "unknown customer" or
an IFERROR around a lookup: they turn a visible error
into a quiet label, and a SUMIFS on names will skip the row. That is
fine when the label is reported, as Femke Jansen's definition of an
active contract does by listing contracts without a known customer
separately (see
define the measure before you count), and harmful when it hides the problem.
INDEX and MATCH
Before XLOOKUP, the robust lookup was
INDEX and MATCH:
MATCH(value, range, 0) returns the position of an exact
match in a range, and INDEX(range, position)
returns the item at that position, so
=INDEX(F$2:F$4,MATCH(B6,E$2:E$4,0)) (row 6 above) does
what XLOOKUP does. It works in every version of Excel, LibreOffice
and Google Sheets, which makes it the safe choice for a workbook
that people open in older versions. Note the 0: MATCH's
default is an approximate match.
The VLOOKUP traps
VLOOKUP(value, table, column_number, [range_lookup]) is
still the most common lookup, and it has two traps. First, the
fourth argument defaults to TRUE, an approximate match that assumes
the first column is sorted. On an unsorted list it does not report a
problem; it returns whatever its search lands on:
| A | B | C | D | E
1 | meter_id | tariff | | Look up | Result
2 | M-30010 | fixed | | M-30006, approximate | =VLOOKUP("M-30006",A2:B5,2)
3 | M-30002 | day_night | | M-30006, exact | =VLOOKUP("M-30006",A2:B5,2,FALSE)
4 | M-30008 | fixed | | M-30005, approximate | =VLOOKUP("M-30005",A2:B5,2)
5 | M-30005 | variable | | M-30005, exact | =VLOOKUP("M-30005",A2:B5,2,FALSE)| A | B | D | E 1 | meter_id | tariff | Look up | Result 2 | M-30010 | fixed | M-30006, approximate | day_night 3 | M-30002 | day_night | M-30006, exact | #N/A 4 | M-30008 | fixed | M-30005, approximate | day_night 5 | M-30005 | variable | M-30005, exact | variable
M-30006 is not in the list, yet the approximate lookup answers
"day_night". M-30005 is in the list, and the approximate
lookup still answers "day_night" instead of "variable". Which wrong
answer you get depends on the program's search method, so another
spreadsheet program can give a different wrong answer. Always pass
FALSE (or 0) unless you mean an approximate match on
sorted data. Second, column_number is a typed number:
insert a column into the table and 2 now fetches the
wrong column, without an error. XLOOKUP and INDEX/MATCH refer to the
return range itself and do not have this problem.
Approximate match on purpose: prices by date
Sometimes an approximate match is exactly right. Kestrel's variable
tariff cost €0.2500 per kWh from 1 January 2026 and €0.2600 from 1
August. The price for a month is the last price whose
valid_from is on or before that month: an "exact or
next smaller" match. XLOOKUP does it with match mode
-1, and MATCH with match type 1:
| A | B | C | D | E | F
1 | period | price (XLOOKUP) | price (INDEX/MATCH) | | valid_from | price
2 | 2026-06-01 | =XLOOKUP(A2,E$2:E$3,F$2:F$3,,-1) | =INDEX(F$2:F$3,MATCH(A2,E$2:E$3,1)) | | 2026-01-01 | 0.2500
3 | 2026-07-01 | =XLOOKUP(A3,E$2:E$3,F$2:F$3,,-1) | =INDEX(F$2:F$3,MATCH(A3,E$2:E$3,1)) | | 2026-08-01 | 0.2600
4 | 2026-08-01 | =XLOOKUP(A4,E$2:E$3,F$2:F$3,,-1) | =INDEX(F$2:F$3,MATCH(A4,E$2:E$3,1))
5 | 2026-09-01 | =XLOOKUP(A5,E$2:E$3,F$2:F$3,,-1) | =INDEX(F$2:F$3,MATCH(A5,E$2:E$3,1))
6 | 2026-09-01 | =XLOOKUP(A6,E$2:E$3,F$2:F$3) | =VLOOKUP(A6,E$2:F$3,2,TRUE)| A | B | C | E | F 1 | period | price (XLOOKUP) | price (INDEX/MATCH) | valid_from | price 2 | 2026-06-01 | 0.25 | 0.25 | 2026-01-01 | 0.25 3 | 2026-07-01 | 0.25 | 0.25 | 2026-08-01 | 0.26 4 | 2026-08-01 | 0.26 | 0.26 | | 5 | 2026-09-01 | 0.26 | 0.26 | | 6 | 2026-09-01 | #N/A | 0.26 | |
Row 6 shows both sides of the coin: an exact XLOOKUP finds no price
"valid from 1 September" and gives #N/A, while VLOOKUP
with TRUE, on this sorted table, is correct. In DuckDB the same
lookup is an ASOF JOIN (see
Analytical SQL). MATCH with type 1
and VLOOKUP with TRUE need the table sorted by
valid_from. XLOOKUP's match mode -1 does
not (only its binary search modes 2 and -2 do). Dates must be real
dates in every case: typed as 2026-08-01 they are, but
a date that arrived as text compares as text.
Text and date functions
Exports rarely arrive clean. A handful of text functions repair most
problems in a check column, without touching the raw data:
TRIM removes leading, trailing and doubled spaces,
UPPER, LOWER and PROPER fix
case, LEFT, RIGHT, MID and
LEN cut and measure, and
SUBSTITUTE replaces characters.
NUMBERVALUE(text, decimal_separator)
turns a number written as text with a decimal comma into a real
number, which is the fix for a Dutch export opened in an English
setup (see
CSV dialects).
Dates are numbers: a serial count of days, shown in a date format.
That makes date arithmetic easy (subtract two dates to get days) and
has one surprise: a formula that returns a date may show the bare
serial number until the cell is formatted as a date.
EOMONTH(date, months) gives the last day of a month,
WORKDAY(date, days) skips weekends (and optionally a
list of holidays), and TEXT(value, format) turns a
value into formatted text.
| A | B
1 | Raw city | ' den haag
2 | Cleaned | =PROPER(TRIM(B1))
3 | Raw amount (text) | '12,50
4 | As a number | =NUMBERVALUE(B3,",")
5 | Report date | 2026-10-06
6 | Last month end | =EOMONTH(B5,-1)
7 | Shown as a date | =TEXT(B6,"yyyy-mm-dd")
8 | Month label | =TEXT(B6,"yyyy-mm")
9 | 5th working day | =TEXT(WORKDAY(B6,5),"yyyy-mm-dd")
10 | Days to due date | =DATE(2026,10,15)-B5| A | B 1 | Raw city | den haag 2 | Cleaned | Den Haag 3 | Raw amount (text) | 12,50 4 | As a number | 12.5 5 | Report date | 2026-10-06 6 | Last month end | 46295 7 | Shown as a date | 2026-09-30 8 | Month label | 2026-09 9 | 5th working day | 2026-10-07 10 | Days to due date | 9
46295 is 30 September 2026 as a serial number; formatting B6 as a
date would show it as one. The KPI pack is due on the fifth working
day of the month, Wednesday 7 October, and September's invoices fall
due on 15 October, nine days after the report date. Typing dates in
ISO 8601 form (2026-10-06) is the safe habit: every
locale reads it the same way, unlike 10/06/2026, which
is 6 October in the United States and 10 June in the Netherlands and
the UK (see
dates and times).
Dynamic arrays: FILTER, UNIQUE and SORT
Classic formulas return one value. A
dynamic array formula
returns a whole range of values from a single formula: a filtered
list, the distinct values of a column, a sorted copy. In Excel 2021,
2024 and Microsoft 365 the result
spills: in Microsoft's words, "a
formula has resulted in multiple values, and those values have been
placed in the neighboring cells". The spill range is that block of
cells, outlined when selected; you refer to all of it by the top
cell followed by #, as in E2#. If any cell
in the way is not empty, the formula shows
#SPILL! until you clear it.
LibreOffice Calc has FILTER, UNIQUE, SORT, SORTBY and SEQUENCE since
version 24.8, but version 25.2 does not spill a normal formula:
select the output range and enter the formula with Ctrl+Shift+Enter
as an array formula. (The grids on these pages are entered that way
automatically, sized to the result.) The # reference is
Excel syntax; in Calc refer to the range itself.
| A | B | C | D | E | F
1 | invoice_id | amount | status | | Paid in September |
2 | INV-40026 | 86.35 | open | | =FILTER(A2:B13,C2:C13="paid") |
3 | INV-40027 | 88.10 | open
4 | INV-40028 | 66.95 | open
5 | INV-40029 | 62.30 | open | | status | amount
6 | INV-40030 | 640.10 | paid | | =UNIQUE(C2:C13) | =SUMIFS(B$2:B$13,C$2:C$13,E6)
7 | INV-40031 | 95.60 | open | | | =SUMIFS(B$2:B$13,C$2:C$13,E7)
8 | INV-40032 | 191.40 | paid
9 | INV-40033 | 55.20 | open
10 | INV-40034 | 94.75 | open
11 | INV-40035 | 452.90 | open
12 | INV-40036 | 58.80 | open
13 | INV-40037 | 71.90 | open| A | B | C | E | F 1 | invoice_id | amount | status | Paid in September | 2 | INV-40026 | 86.35 | open | INV-40030 | 640.1 3 | INV-40027 | 88.1 | open | INV-40032 | 191.4 4 | INV-40028 | 66.95 | open | | 5 | INV-40029 | 62.3 | open | status | amount 6 | INV-40030 | 640.1 | paid | open | 1132.85 7 | INV-40031 | 95.6 | open | paid | 831.5 8 | INV-40032 | 191.4 | paid | | 9 | INV-40033 | 55.2 | open | | 10 | INV-40034 | 94.75 | open | | 11 | INV-40035 | 452.9 | open | | 12 | INV-40036 | 58.8 | open | | 13 | INV-40037 | 71.9 | open | |
These are Kestrel's twelve September invoices. FILTER returns the
two paid ones (the bakery and the bicycle repair shop pay by direct
debit), with both columns. UNIQUE lists each status once, in the
order they first appear, and SUMIFS next to it adds up each status:
€1,132.85 open and €831.50 paid, the same totals as the GROUPING
SETS query on
Analytical SQL. In Excel you would
write =SUMIFS(B2:B13,C2:C13,E6#) once and let it spill
along with the UNIQUE list; in Calc 25.2 the SUMIFS is copied down
by hand, and must be extended if a new status appears. FILTER also
accepts a third argument with what to show when nothing matches;
without it, an empty result is an error.
Excel-only functions
Microsoft 365 goes further. GROUPBY builds the whole
summary above in one formula, with a total row (as of October 2026
only in Excel for Microsoft 365; it is explained with pivot tables
on
Pivot Tables and Summaries). It is not in LibreOffice or Google Sheets, so it cannot be run
here:
=GROUPBY(B2:B13, C2:C13, SUM) B = tariff, C = kwh, the meter table under SUMIFS aboveday_night 1828.518 dynamic 2057.276 fixed 1074.24 variable 597.561 Total 5557.595
The Calc-compatible alternative is the pattern just shown: UNIQUE
(or SORT of UNIQUE) for the groups and SUMIFS beside it. The same
goes for TEXTSPLIT, VSTACK,
TAKE and DROP: Excel 2024 and Microsoft
365 have them, LibreOffice only from version 25.8. In Calc 25.2,
split text with LEFT, MID and
FIND, or better, split columns when the data is
imported.
LET: names inside a formula
Long formulas repeat themselves. The
LET function assigns names to
intermediate results inside one formula and then uses them:
LET(name1, value1, name2, value2, …, calculation). Each
value is calculated once, and the formula reads like a small program
instead of a puzzle. LET is in Excel 2021 and later, Google Sheets,
and LibreOffice from 24.8.
| A | B
1 | kwh | 1074.240
2 | price | 0.2700
3 | VAT rate | 21%
4 | Without LET | =ROUND(B1*B2+B1*B2*B3,2)
5 | With LET | =LET(net,B1*B2,vat,net*B3,ROUND(net+vat,2))| A | B 1 | kwh | 1074.24 2 | price | 0.27 3 | VAT rate | 21.00% 4 | Without LET | 350.95 5 | With LET | 350.95
The fixed tariff's September energy at €0.27 per kWh plus 21% VAT,
as a simplified illustration: Kestrel's real invoices also contain a
standing charge and energy tax, which is why they do not reconcile
with readings times price. Typing 21% stores 0.21 and
formats the cell as a percentage. The names in LET exist only inside
that one formula; for a name the whole workbook can use, define a
named range.
Giving a calculation a name is the same idea as
abstraction
in programming.
Excel 2024, Microsoft 365 and Google Sheets go one step further with
LAMBDA, which turns a formula into a reusable function
with parameters, usually saved under a name in Name Manager.
LibreOffice has no LAMBDA in any release as of October 2026, so this
is illustrative:
Name: CostInclVAT
Refers to: =LAMBDA(kwh, price, vat, ROUND(kwh*price*(1+vat), 2))
In a cell: =CostInclVAT(B1, B2, B3)350.95
Versions and languages
A formula that works on your machine can fail on Margriet's, for two reasons: her program may not have the function, or it may expect it in another language.
| Function | Excel 365 | Excel 2024 | Excel 2021 | Excel 2019 | LibreOffice | Google Sheets |
|---|---|---|---|---|---|---|
| IFS, SUMIFS, COUNTIFS, INDEX/MATCH | Yes | Yes | Yes | Yes | Yes | Yes |
| XLOOKUP, FILTER, UNIQUE, SORT, SEQUENCE, LET | Yes | Yes | Yes | No | 24.8 and later | Yes |
| XMATCH, SORTBY | Yes | Yes | Yes | No | 24.8 and later | No |
| TEXTSPLIT, VSTACK, HSTACK, TAKE, DROP | Yes | Yes | No | No | 25.8 and later | VSTACK and HSTACK only (SPLIT instead of TEXTSPLIT) |
| LAMBDA | Yes | Yes | No | No | No | Yes |
| GROUPBY, PIVOTBY | Yes | No | No | No | No | No |
Microsoft's own XLOOKUP page lists Excel 2019 under "Applies to",
but its text says the function is not available in Excel 2016 and
Excel 2019; the text is right. The PIVOTBY page similarly lists
Excel 2021 and 2024, which is almost certainly a mistake: treat
PIVOTBY, like its twin GROUPBY, as Microsoft 365 only. When you
share a workbook with someone on an older version, a missing
function shows up as #NAME?.