Three AI Assistants, Three Different Answers — Which Excel Formula is Actually Best?
I used to spend way too much time building and debugging complex Excel formulas. Now I can describe a problem to AI and get a working solution in seconds. But when I asked ChatGPT, Claude, and Gemini for the “best” Excel formula, they all gave me completely different answers — and that taught me something important about relying on AI for spreadsheet work.
The Test: One Prompt, Three Formulas
For a fair comparison, I gave each AI assistant the exact same prompt. No Microsoft Copilot — just standalone models starting from the same instructions.
The scenario: I have an Excel table named T_Sales with columns Product, Jan, Feb, Mar, Apr, May, Jun, and a blank column called “First Month Over 10000.” I need a formula that returns the first month where sales exceed 10,000 for each row. If no month qualifies, return “None.”
ChatGPT suggested:
=LET(sales, T_Sales[@[Jan]:[Jun]], months, T_Sales[[#Headers],[Jan]:[Jun]], match_pos, XMATCH(TRUE, sales>10000), IFERROR(INDEX(months, match_pos), "None"))
Claude suggested:
=LET(sales_data, T_Sales[@[Jan]:[Jun]], month_names, {"Jan","Feb","Mar","Apr","May","Jun"}, match_idx, XMATCH(TRUE, sales_data>10000), IF(ISNA(match_idx), "None", INDEX(month_names, match_idx)))
Gemini suggested two options:
=XLOOKUP(TRUE, T_Sales[@[Jan]:[Jun]]>10000, {"Jan","Feb","Mar","Apr","May","Jun"}, "None", 1)
and
=XLOOKUP(TRUE, T_Sales[@[Jan]:[Jun]]>10000, T_Sales[[#Headers],[Jan]:[Jun]], "None", 0)
Why the “Best” Formula Depends on Your Priorities
All three formulas worked when tested against real data. They returned the correct months. But each AI made different tradeoffs to get there.
ChatGPT and Claude both used the LET function, prioritizing readability through named variables. This is great for complex spreadsheets that others will maintain. But AI can sometimes over-engineer a simple solution.
ChatGPT’s formula and Gemini’s second option are more dynamic — they pull month names directly from table headers (T_Sales[[#Headers],[Jan]:[Jun]]), so changes to those headers automatically update the formula.
Claude’s formula and Gemini’s first option hard-coded the month names, meaning any header changes require manual formula updates.
Gemini prioritized brevity with XLOOKUP, avoiding the separate INDEX and XMATCH functions. But by skipping LET, the formulas lack named variables that make complex logic easier to understand. Gemini also included unnecessary match mode arguments that don’t change the outcome — a reminder that AI can produce working but not always optimal code.
The Real Lesson: AI as Tutor, Not Author
None of the formulas were wrong. Excel often has multiple legitimate ways to solve the same problem. But “best” depends on factors a prompt can’t fully capture — who will maintain the workbook, how it fits into existing spreadsheets, and what tradeoffs matter in your specific context.
AI can help you explore solutions and explain complex functions. But the final call is still yours. Test formulas against real data, verify they integrate with your workbook, and make sure they’re understandable to the people who will maintain them.
Getting Better Results from AI for Excel
To improve spreadsheet outcomes from AI, give it clear boundaries:
- Specify your exact Excel version to avoid compatibility issues
- Provide a small, representative sample of your data structure
- Clearly state the expected output and any approaches to prefer or avoid
- Mention the skill level of spreadsheet users who will maintain the file
- Indicate whether helper columns are acceptable
Once AI suggests a solution, the final step is still yours — test it, verify it, and make sure it serves the people who will actually use it.
