I asked ChatGPT, Claude, and Gemini for the best Excel formula—they all disagreed

Published Jul 20, 2026, 6:30 AM EDT Tony Phillips is an experienced Microsoft Office user with a dual-honors degree in Linguistics and Hispanic Studies. Prior to starting with How-to Geek in January 2024, he worked as a document producer, data manager, and content creator for over ten years, and loves making spreadsheets and documents in his spare time. Tony is also an academic proofreader, experienced in reading, editing, and formatting over 3 million words of personal statements, resumes, reference letters, research proposals, and dissertations. Before joining How-To Geek, Tony formatted and wrote documents for legal firms, including contracts, Wills, and Powers of Attorney. Tony is obsessed with Microsoft Office! He will find any reason to create a spreadsheet, exploring ways to add complex formulas and discover new ways to make data tick. He also takes pride in producing Word documents that look the part. He has worked as a data manager in a secondary school in the UK and has years of experience in the classroom with Microsoft PowerPoint. He loves to encounter problems in Microsoft Office and use his expertise and legal-level training to find solutions. Outside of the Microsoft world, Tony is a keen dog owner and lover, football fan, astrophotographer, gardener, and golfer. I used to spend far too much time building and debugging complex Excel formulas. Now, I can describe a problem to AI and get a working answer in seconds. But when I asked ChatGPT, Claude, and Gemini for the "best" Excel formula, they all gave me different answers. Three assistants solved the same problem differently One prompt, three different formulas For this test, I gave ChatGPT, Claude, and Gemini exactly the same prompt. I used standalone AI assistants rather than Microsoft Copilot so each model worked from identical instructions and context. Here's what I said: I have an Excel table named T_Sales. It contains the columns Product, Jan, Feb, Mar, Apr, May, Jun, and a blank column called First Month Over 10000. I need a formula for the First Month Over 10000 column that returns the first month where that row's sales exceed 10,000. If no month exceeds 10,000, return "None". I'm using Microsoft 365 Excel. Please provide the best formula and briefly explain why you chose that approach. This wasn't intended as a scientific benchmark—one prompt cannot represent every possible AI response. Instead, my aim was to see how different AI assistants approached the same Excel problem. Even with the same prompt, the same goal, and a straightforward task, each AI chose a different approach. 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 provided 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) The best formula depends on your priorities Each AI chose a different tradeoff When I tested each formula on my data in Excel, all of them returned the expected months. But where they differed was the design choice each AI made to get there. ChatGPT and Claude both suggested the LET function, which allowed them to structure their formulas around readability and organization rather than brevity. While this is beneficial for making complex formulas easier to follow, there's also the risk that AI can make a formula more sophisticated than necessary for the problem being solved. ChatGPT's formula and Gemini's second formula are arguably the most dynamic. Because they pull the month labels directly from the table headers (T_Sales[[#Headers],[Jan]:[Jun]]), Excel automatically updates the references if those headers change. Claude's formula and Gemini's first formula are less dynamic, however. Since they hard-coded the month names into the formula ({"Jan","Feb","Mar","Apr","May","Jun"}), changing the table headers would also require updating the formula manually. Gemini prioritized brevity. Its XLOOKUP approach avoids the separate INDEX and XMATCH functions used in the other formulas, resulting in shorter formulas. However, by avoiding LET, Gemini's formulas don't use named variables, which can make longer formulas easier to understand. Gemini also included unnecessary match mode arguments (0 and 1) in both formulas. They don't change the result in this scenario, but they show how AI can confidently produce formulas that work without necessarily producing the cleanest option. They also highlight that AI responses can vary even when solving the same problem, with Gemini itself suggesting two slightly different approaches within the same conversation. The explanations each AI provided were also revealing. They explained why their formulas worked, but they didn't address the bigger design questions: whether the formulas would be easy for another person to maintain, whether they would adapt to future changes, or whether a simpler approach might be more appropriate. That's the limitation of asking AI for the "best" formula. It can evaluate technical correctness, but it can't know the priorities of the people who will actually use the workbook. The "best" formula depends on you Good prompts still need human judgment Credit: Lucas Gouveia / How-To Geek None of the formulas were wrong. Excel often has multiple valid ways to solve the same problem, but "best" depends on factors that are difficult to capture in a prompt. You can tell AI which version of Excel you use, whether you prefer newer functions, or whether helper columns are acceptable. However, it can't reliably know why certain decisions were made in an existing workbook, which formulas your colleagues understand, or which unusual-looking approaches exist for a specific reason. Uploading a workbook gives AI more context, but it still doesn't provide the reasoning behind those decisions. It can see the formulas and data, but it can't always understand the history, priorities, or constraints that shaped the workbook. You also need to consider whether the workbook contains sensitive information before sharing it with an AI service. But giving accurate prompts becomes increasingly difficult as spreadsheets grow. A complex forecasting model or reporting workbook may contain years of assumptions, workarounds, and design choices spread across multiple sheets. At some point, explaining all of that context to AI can remove much of the convenience it was supposed to provide. AI can help you explore possible solutions, but choosing the right approach still requires someone who understands the purpose of the workbook. Use AI as a formula tutor, not an author Let AI help, but make the final call Credit: Lucas Gouveia / How-To Geek You don't need to avoid AI in your spreadsheet workflow. Instead, treat it as a tool for exploring ideas, explaining formulas, and suggesting possible solutions—not as the final decision-maker. To get better spreadsheet results from AI, give it clear boundaries: Specify the exact version of Excel you're using to prevent compatibility issues. Provide a small, representative sample of your data structure. Clearly explain the expected output and any approaches you want to consider or avoid. Explain the level of expertise of the people who will use or maintain the spreadsheet. Mention any functions you want to avoid or prefer. Explain whether helper columns are acceptable. Once AI has suggested a solution, the final step is still yours. Test the formula against real data, verify that it works in your workbook, and make sure it is understandable to the people who will maintain it. Use AI where it adds value None of this means AI has no place in your spreadsheet workflow. When you give it a clearly scoped task and the right boundaries, it's genuinely useful for explaining formulas, generating test data, and automating repetitive steps. I recently put this approach to the test by asking Claude to build a set of Excel automations from scratch. It was surprisingly capable of creating tools that saved time—but I still needed to test each one before relying on it.