[Spreadsheet]AI-Written Complex Formulas & Arrays

Summary: Use AI to write complex formulas like XLOOKUP and dynamic arrays for you—copy, paste, and they just work.

I. Pain Points

Have you ever had your boss drop a spreadsheet on your desk and say, "Pull out the regional price for each customer"? Your mind immediately jumps to VLOOKUP, then you need to wrap another layer of IFERROR around it to suppress errors—and by the third nested layer you've tied yourself in knots, hit Enter, and the whole screen lights up with #N/A.

Honestly, the biggest hurdle with complex Excel formulas isn't that they're "hard"—it's that you "can't remember the arguments." The function names run on forever, the parentheses nest inside each other, and you don't even know where to put the cursor. In the end, a lot of people just copy a formula from a forum, paste it in, and only then realize it references someone else's table—and there's no way to adapt it.

There's an easier way now: hand this job to AI to write the formulas. We recommend web-based AI first—Qwen (千问) (tongyi.aliyun.com) and ChatGPT (chatgpt.com) open right in your browser, no matter which Excel version is installed locally. You paste in your table structure and requirements, and it spits out a formula you can drop straight into a cell. Excel's built-in Copilot can do the same thing, but only if your organization has a Microsoft 365 Copilot license—it isn't there by default, and without it you won't even find the button in Excel. If you do have it, the same prompts work just as well in its chat box. This article demos with the web-based AI, and every formula comes with real sample data so you can paste it in and check whether it's right.

II. What You'll Achieve

After reading, you'll be able to have AI knock out these four categories of "hardcore" formulas in one go, each with sample data and expected results so you can verify by pasting and calculating:

Multi-level lookup: Use XLOOKUP (the newer function in Excel 365 / 2021+ for "looking up a value by key," smarter than the old VLOOKUP) for two-dimensional lookups like "product × region."
Error handling: Use IFERROR (a fallback function that "shows something else if the calculation fails") to swap the harsh #N/A for a friendly message.
Text joining: Use TEXTJOIN (the function that "glues a bunch of text into one string") to pack filtered results into a single cell.
Dynamic arrays: Use the five new functions FILTER / SORT / UNIQUE / SEQUENCE / LET—whose results automatically spill to fill a range of cells—for cleaning and dashboards.

Prerequisites (split into three tiers by version): ① Functions like IFERROR and TEXTJOIN work in Excel 2019 and later; ② XLOOKUP is not in Excel 2019 (perpetual)—it requires Microsoft 365 or Excel 2021 and later (same tier as dynamic arrays); ③ Dynamic arrays (FILTER / SORT / UNIQUE / SEQUENCE / LET) likewise require Excel 365 or 2021 and later. These functions don't exist in older versions (2016 and earlier)—the "Pitfall Guide" later explains what to do. The AI tools we recommend are web-based (Qwen / ChatGPT), which don't care about your Excel version and work instantly; if your org has an M365 Copilot license, you can also use the Copilot inside Excel, and the prompts are the same.

III. Hands-on Examples

First, set up two tables as your "targets."

Table 1: Price Matrix (place on Sheet "Price", A1:D4)

Cell	A	B	C	D
Row 1	Product	East	North	South
Row 2	Apple	5	6	7
Row 3	Banana	4	5	6
Row 4	Orange	8	9	10

Table 2: Order Details (place on Sheet "Orders", A1:C6)

Cell	A Order ID	B Customer	C Amount
Row 1	Order ID	Customer	Amount
Row 2	O001	Zhang San	100
Row 3	O002	Li Si	200
Row 4	O003	Zhang San	150
Row 5	O004	Wang Wu	80
Row 6	O005	Zhang San	300

Scenario 1: Multi-level Lookup with XLOOKUP

Step 1: Tell your need to the AI (prompt)

In the chat box of web-based Qwen / ChatGPT (or Excel Copilot if enabled), type the following (copy as-is, swap the table names for your own):

I have a price table in Excel where column A is the product (A2:A4), row 1 (B1:D1) is the region, and B2:D4 is the price. Write an XLOOKUP formula that looks up the price of "Banana" in the "North" region.

Step 2: Get the formula and verify

The AI will usually give you:

excel
=XLOOKUP("Banana", A2:A4, XLOOKUP("North", B1:D1, B2:D4))

Expected result: 5 (the value at the intersection of the "Banana" row and the "North" column).
Plain-English explanation: This is a "two-level XLOOKUP" (also called nested XLOOKUP). The inner XLOOKUP("North", B1:D1, B2:D4) first finds "North" among the region names and pulls out that entire column of prices; the outer one then locates the row for "Banana" in the product column and finally returns the 5 at the intersection.

Key point: With multi-level XLOOKUP you don't have to keep the "lookup column" on the far left like old VLOOKUP forced you to—rows and columns can be arranged however you like. That's the core reason it beats VLOOKUP.

Scenario 2: IFERROR for Error Handling

Step 1: Have the AI "wrap" the formula

Prompt:

Wrap the XLOOKUP formula above in an IFERROR. If the lookup fails (e.g., the product doesn't exist), show "No price" instead of #N/A.

Step 2: Verify the not-found case

The AI gives you:

excel
=IFERROR(XLOOKUP("Mango", A2:A4, XLOOKUP("North", B1:D1, B2:D4)), "No price")

Expected result: No price ("Mango" isn't in the product column, so it would normally throw #N/A—IFERROR catches it and swaps in the text).
Plain-English explanation: IFERROR's rule is "calculate what's inside the parentheses first; if anything errors, show the second argument." That way the table looks clean for your boss, with no red error text popping up.

Scenario 3: TEXTJOIN for Joining (combined with FILTER)

Version note: In this example, TEXTJOIN itself works in Excel 2019+, but it combines FILTER (a dynamic array function), so the whole formula still requires Excel 365 or 2021 and later to run; in the 2019 perpetual version it will throw #NAME? directly.

Step 1: Have the AI write "filter + join"

Prompt:

In the order table, column A is the order ID and column B is the customer. Write a formula that joins all order IDs belonging to "Zhang San" into one cell, separated by a comma and a space.

Step 2: Verify the join result

The AI gives you:

excel
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6="Zhang San"))

Expected result: O001, O003, O005 (Zhang San has three orders in the table: O001, O003, O005).
Plain-English explanation: FILTER(A2:A6, B2:B6="Zhang San") first screens out Zhang San's order IDs, producing a set of values {O001; O003; O005}; TEXTJOIN then glues them into one long string with ", ". The second argument TRUE means "skip empty cells, ignore them."

Key point: TEXTJOIN itself doesn't filter—it only "glues." To join by condition, you first let FILTER pick out the matching values, then hand them to TEXTJOIN to glue—this is the most common "one-two combo" of dynamic array functions.

Scenario 4: The Dynamic Array Five-Piece Set

Dynamic array = a new kind of formula whose set of results automatically "spills" into adjacent blank cells, no manual dragging needed. This is Excel 365's forte. Let's go through all five; the prompts are written for you—paste and go.

(1) FILTER—filter by condition

Prompt: "Filter out all rows in the order table where the customer equals Zhang San."

excel
=FILTER(A2:C6, B2:B6="Zhang San")

Expected result: spills into 3 rows × 3 columns: O001 Zhang San 100 / O003 Zhang San 150 / O005 Zhang San 300.

(2) SORT—sort

Prompt: "Sort A2:C6 by the 3rd column (amount) from largest to smallest."

excel
=SORT(A2:C6, 3, -1)

Expected result: in descending amount order: O005 Zhang San 300 / O002 Li Si 200 / O003 Zhang San 150 / O001 Zhang San 100 / O004 Wang Wu 80. The third argument -1 means "descending"; 1 would be ascending.

(3) UNIQUE—remove duplicates

Prompt: "Remove duplicates from the customer names in B2:B6, listing only the distinct ones."

excel
=UNIQUE(B2:B6)

Expected result: Zhang San / Li Si / Wang Wu (originally 5 entries with 3 Zhang San; after dedup only one remains).

(4) SEQUENCE—generate a sequence

Prompt: "Generate consecutive numbers 1 through 5 to fill a column."

excel
=SEQUENCE(5)

Expected result: 1 / 2 / 3 / 4 / 5. Often used as a helper sequence column, or as the "row number" for other formulas (e.g., with INDEX to grab the Nth row).

(5) LET—name intermediate results so the formula reads better

Prompt: "Write a formula with LET: first define a threshold of 100, then filter the order IDs whose amount is greater than 100."

excel
=LET(threshold, 100, orders, A2:C6, amount, INDEX(orders, 0, 3), FILTER(INDEX(orders, 0, 1), amount > threshold))

Expected result: O002 / O003 / O005 (amounts 200, 150, 300 are all greater than 100).
Plain-English explanation: LET lets you first give names to intermediate quantities like "threshold," "orders," "amount," then reuse them directly—so even a long formula stays manageable. In INDEX(orders, 0, 3), the 0 means "take the entire column" (the 3rd column is the amount column).

Uncertain point (please verify by testing yourself): Using Chinese as variable names inside LET (like "阈值") is supported in Excel 365, but a very few old patch versions or certain regional settings may throw an error; if that happens, just switch the variable names to English (e.g., th, amt)—the logic is exactly the same.

IV. How It Works

Old Excel's array formulas had to be entered with Ctrl+Shift+Enter (the so-called "CSE array formula"), and they easily errored out across the whole range if the selection was off. Excel 365's "dynamic arrays" completely changed the game—one formula computes a set of results that automatically "spills" into adjacent blank cells, no more manual dragging. The reason XLOOKUP can replace VLOOKUP is that it doesn't have to obey the iron rule of "the lookup column must be leftmost," and it can directly specify "what to return when not found," naturally decoupling it from IFERROR. TEXTJOIN folds the two annoying tasks of "joining + ignoring blanks" into a single function. And LET gives names to repeated calculations inside a formula, boosting performance and readability. In one sentence: these new functions make "complex" "breakable into pieces," and with AI flattening the syntax barrier too, ordinary people can now write formulas they once wouldn't touch without a book.

V. Pitfall Guide

The "lookup array" and "return array" in XLOOKUP must have matching row counts. The first argument (what to find) and second argument (where to find it) must be the same length; in a multi-level XLOOKUP, the column returned by the inner lookup must also match the row count of the outer lookup column, or you'll get #VALUE!.
Dynamic arrays "spill"—don't park data beneath them. The results of formulas like FILTER / SORT automatically spread downward / rightward. If you've manually typed something directly below, you'll get #SPILL! ("spill blocked"). Just clear the cells below.
Don't omit TEXTJOIN's second argument. It must be TRUE to skip empty cells; if you write FALSE or leave it out, empty cells still take up a delimiter, producing an odd look like ", , ".
Don't wrap too much in IFERROR. It's "aggressive"—it swallows every error inside it (including genuine mistakes where you wrote the formula wrong). When debugging, first strip off the IFERROR to see the raw error, locate the problem, then add it back.
Always test the AI's formula once with "sample data + expected results" first. Don't paste it straight onto your boss's production table. The AI occasionally counts the header row or references the wrong range; verifying with the sample above is the safest bet.

VI. Going Further

Back to B2 (Data Cleaning): If the raw table you get is a mess (merged cells, extra spaces, dates stored as text), first go to B2 and clean it in one click with "Qwen (千问)," then come back to apply the formulas here—so dirty data doesn't throw your formulas off.
Forward to the next level, B4 (Financial Models): When you want to take these lookup and filter results and build them into profit statements and cash-flow forecasts, move on to B4 to learn how to build linked financial models.
Deeper play: For multi-dimensional summaries you can turn to PivotTable with drag-and-drop analysis; to write cross-table measures, learn DAX (the dedicated calculation language in Power BI / Excel's data model); and to automatically merge and clean dozens of tables, rely on Power Query (the "Get Data / Queries" ETL tool in Excel). Combined with the formulas in this article, these three cover roughly 90% of workplace data work.

🛠️ AI-Written Complex Formulas & Arrays