Excel Advanced Functions: Let AI Draft Them, You Test Them
Excel advanced functions like XLOOKUP, INDEX/MATCH and FILTER, where AI actually helps write one, a worked example, and the checks before you trust it.
"Advanced" in Excel used to mean nested IFs and array formulas entered with Ctrl+Shift+Enter. It means something different now: XLOOKUP replacing VLOOKUP for most lookups, and a set of dynamic array functions — FILTER, SORT, UNIQUE — that spill a whole result across cells from a single formula instead of being copied down one row at a time. An AI chat tool is genuinely useful for picking between them and writing the syntax. It is not useful for knowing which one your version of Excel actually supports, and that gap is where most of the wasted time happens.
Where AI actually helps
The task AI is good at is translation: you describe the job in plain language, and it produces the formula, including the newer functions a lot of long-time Excel users have never had a reason to learn. OpenAI's and Anthropic's own prompting guidance both make the same point that applies here: state the actual job, the actual column layout, and what should happen on a miss, and the formula that comes back needs far less fixing. "Write me a lookup formula" gets a VLOOKUP, because VLOOKUP is the most common answer online to a vague lookup question — not because it's the best fit for your sheet.
A worked example
Weak: "I need a formula to find a price."
Better: "I have a Products sheet with product IDs in column A and prices in column B, and an Orders sheet where column A has a product ID and column B should show its price. Write a formula for Orders column B that looks up the ID on the Products sheet and returns the price, shows 'Not found' if the ID doesn't exist, and keeps working if someone inserts a new column into the Products sheet."
A reasonable answer is a single XLOOKUP: =XLOOKUP(A2, Products!A:A, Products!B:B, "Not found"). It beats a VLOOKUP here for two concrete reasons that follow directly from the request — VLOOKUP breaks if a column gets inserted between the lookup and return columns, because it counts columns by position, and VLOOKUP needs a separate IFERROR wrapper to show "Not found" instead of a raw #N/A, where XLOOKUP takes that as a built-in fourth argument.
If the job instead needs every matching row rather than the first one — all orders for a given customer, say — that's a FILTER, not a lookup at all: =FILTER(Orders!A:C, Orders!B:B="Customer Name"). Asking for "every row where" rather than "the value where" is the signal that tells a chat tool, and a person, which family of function actually fits, and code for Excel covers the same translate-the-job-first step for tasks that need VBA rather than a worksheet formula.
Once that FILTER is in the sheet, it spills — the result fills as many cells below and to the right as it needs, on its own, without you copying the formula down a column. That is the real difference dynamic array functions made: a formula used to live in one cell and get copied; now one formula can be the whole table. It also means something new can go wrong — a spill gets blocked with a #SPILL! error if there's anything, even an empty-looking cell with a stray space in it, sitting in the space the result needs to expand into.
A third family covers conditional totals — SUMIFS, COUNTIFS, AVERAGEIFS — and these are the ones a chat tool gets wrong most often in a specific way: nesting too many conditions into one formula when two simpler ones, checked separately, would be easier to trust. Asked for "total sales for the West region in March, for orders over £500", a good answer is one SUMIFS with three criteria ranges, but it is worth asking the tool to also show the three totals separately — West region alone, March alone, orders over £500 alone — so a wrong combined total is obvious against the three it should sit between, rather than a single number you have no way to sanity-check against anything.
Where it breaks
XLOOKUP and the dynamic array functions only exist in Microsoft 365, Excel 2021 and later, and Excel for the web — not Excel 2016 or 2019. A chat tool has no way to know which one you have unless you say so, and it will hand you a modern function without checking, because the request didn't rule it out. Paste that formula into an older Excel and it returns #NAME? instead of a result, which looks like a typo rather than a version mismatch until you've wasted ten minutes checking your spelling.
The deeper failure is the same one that shows up in every AI-written formula: producing fluent, confident output regardless of whether it's actually correct is a documented property of how these models work, and a formula that looks syntactically right but references the wrong column, or off-by-one on a range, reads exactly like a correct one until you run it. Push back and ask the tool to double-check itself, and it will often find a problem whether or not one exists — agreeing that something's wrong is a common way these models respond to being questioned, not evidence the first formula was actually broken.
Checks before you trust the formula
- Test it on two or three rows where you already know the right answer by eye, before applying it to the whole sheet.
- If the formula uses XLOOKUP, FILTER, SORT or UNIQUE, confirm your Excel is Microsoft 365, 2021 or later — a #NAME? error on a formula that looks fine is the single most common sign of this mismatch.
- Insert a column somewhere in the lookup range on purpose and check the formula still returns the right value — this is the exact failure mode XLOOKUP fixes over VLOOKUP, and it's worth seeing it actually hold.
- Read the formula back in plain English yourself before trusting it on live data — if you can't explain why each argument is there, you can't tell when one of them is wrong.
- Keep a version of the sheet before the new formula goes in, the same discipline NIST's AI Risk Management Framework recommends for anything AI-assisted that feeds a decision — a formula error you can roll back is an annoyance, one baked into months of reports is a mess.
AI picks the right function and writes the syntax fast. Whether it is the function your Excel actually runs is still your check to make.
What to do Monday
Take one lookup or filter you currently do by hand or with a clunky nested formula, describe the job precisely — the columns, the layout, what should happen on a miss — and ask for the formula rather than the function name. Test it against known answers before it touches a real report. How to add data analysis in Excel and how to summarise in Excel cover the next step once the lookup itself is solid, and Excel user defined function is worth reading if the same formula ends up getting reused often enough to deserve a name.
If you're starting from a blank sheet rather than fixing an old one, how to create a spreadsheet in Excel and how to use AutoSum in Excel cover the basics first. Coursium teaches this kind of practical AI judgement directly — what to ask for, and what to check once you have it. Stay ahead of AI by learning the tools on your phone.