In short: dynamic arrays, XLOOKUP, FILTER and LET arrived in Excel 2021 and Microsoft 365, but not in Excel 2019 or 2016. LAMBDA and its helpers, along with newer functions such as TAKE and TEXTAFTER, are in Microsoft 365 but not in Excel 2021. If you are unsure which you have, you can check in a minute, and the table below shows what to expect.
Which functions work in which version?
These are the broad positions at the time of writing. Microsoft adds functions to Microsoft 365 first, and later one-time releases catch up in part, so confirm any specific function against Microsoft's own list for your edition.
| Function or feature | Excel 2016 | Excel 2019 | Excel 2021 | Microsoft 365 | Excel for the web |
|---|---|---|---|---|---|
| IFS, MAXIFS, TEXTJOIN | No | Yes | Yes | Yes | Yes |
| Dynamic arrays, FILTER, SORT, UNIQUE | No | No | Yes | Yes | Yes |
| XLOOKUP | No | No | Yes | Yes | Yes |
| LET | No | No | Yes | Yes | Yes |
| LAMBDA, MAP, SCAN, BYROW | No | No | No | Yes | Yes |
| TAKE, TEXTAFTER | No | No | No | Yes | Yes |
| GROUPBY | No | No | No | Newer builds only | Check, as rollouts vary |
The mobile apps usually follow Microsoft 365, sometimes after a delay. A function that works on your laptop may not yet work on a phone, so test on the device that matters.
What do the version names mean?
- Microsoft 365 is the subscription. It keeps getting new functions, so two people on Microsoft 365 can still differ if one has not updated.
- Excel 2021, 2019 and 2016 are one-time purchases. They get fixes, but not new functions after release.
- Excel for the web is free with a Microsoft account and runs in a browser.
Later one-time releases may include more of the newer functions than 2021 does, so check Microsoft's function list for that edition rather than assuming.
How do I check which version I have?
- Open Excel and go to File, then Account. The product name and version appear on that page.
- Look for the words Microsoft 365 at the top of the Account page; if it says Excel 2019 or Excel 2021, you have a one-time edition.
- Or test directly: type =XLOOKUP( into a cell. If Excel suggests the function as you type, you have it.
- Do the same with =LET( or =LAMBDA( for those.
What should I do before sharing a workbook?
Find out the oldest version that will open the file. If your CA, an office laptop or a relative uses Excel 2016 or 2019, any XLOOKUP, FILTER or LET in the file will show errors for them. Two simple habits help: build with the oldest version's functions where it costs you nothing, or send a values-only copy for reading.
A worked example
Say you want to find the category for a merchant. On Microsoft 365 or Excel 2021 you could write =XLOOKUP(A2,$F$2:$F$80,$G$2:$G$80,"Uncategorised"). On Excel 2019 the same job is =IFERROR(INDEX($G$2:$G$80,MATCH(A2,$F$2:$F$80,0)),"Uncategorised"). Both return the category in G for the merchant in A2 and fall back to Uncategorised when the merchant is not found. The second one works everywhere, which is why many people keep using it.
Is it worth upgrading?
Only if a feature saves you real time. Many expense sheets run fine on SUMIFS, INDEX and MATCH, and a pivot table, which every version has. The newer functions make long formulas shorter and easier to read, but they do not unlock anything you cannot do the older way. Pay for an upgrade for something you will use weekly, not for a function you read about.
If the real pain is keeping statements and categories up to date, a sheet may not be the right tool at all; an expense tracker that imports statements does that part for you.
Quick reference
- Excel 2016 and 2019: use SUMIFS, INDEX, MATCH, IFERROR.
- Excel 2021: you also have XLOOKUP, FILTER, SORT, UNIQUE and LET.
- Microsoft 365: you also have LAMBDA, MAP, SCAN, BYROW, TAKE and TEXTAFTER.
- When unsure, type the function name and see whether Excel recognises it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


