Choosing Portable Functions for Spreadsheets Shared Between Excel and Google Sheets — Replacing XLOOKUP with SUMIF for Compatibility

Tadashi Shigeoka reports that XLOOKUP formulas written in Microsoft Excel can return empty or incorrect results when the .xlsx file is opened in Google Sheets, and recommends replacing XLOOKUP with SUMIF for numeric lookups or INDEX with MATCH for string targets to ensure cross-platform compatibility. The article, published August 5, 2026, outlines a set of portable functions—SUMIF, SUMIFS, SUM, ROUND, IF, and INDEX/MATCH—and suggests adding reconciliation cells to surface conversion errors visibly.

Choosing Portable Functions for Spreadsheets Shared Between Excel and Google Sheets — Replacing XLOOKUP with SUMIF for Compatibility Tadashi Shigeoka /en/author/tadashi-shigeoka/ · Wed, August 5, 2026 My current workflow has AI agents like Claude Code https://www.anthropic.com/claude-code and Codex https://openai.com/codex/ doing spreadsheet work locally in Excel https://www.microsoft.com/en-us/microsoft-365/excel , while the shared, human-facing copy lives in Google Sheets https://www.google.com/sheets/about/ . In that setup, function compatibility between the two tools quietly becomes an operational bottleneck. This post starts from a formula I wrote in Excel that broke the moment I opened the file in Google Sheets, and works out to a small set of rules for picking functions that survive the round trip. The Symptom: XLOOKUP Written in Excel Comes Back Empty in Google Sheets Open an Excel-authored .xlsx file in Google Sheets and you can hit a case where the formula is displayed correctly, but the calculated result comes back empty or wrong. The culprit this time was an XLOOKUP https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 that pulled values from a lookup table: XLOOKUP is available in both current Excel and Google Sheets the Google Sheets side is documented in the XLOOKUP function help https://support.google.com/docs/answer/12405947 . So it isn’t a plain “unsupported in Google Sheets” problem. The actual failure modes are more like: - How .xlsx formulas get translated into Google Sheets formulas - How stored, precomputed cell values are treated - Recalculation timing when the file is opened - Excel-specific formula representation and internal function names - Dynamic array interpretation - Reference range and blank-cell handling For the “look up a value by key and aggregate a number” case, I replaced XLOOKUP with the more elementary SUMIF https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b : XLOOKUP and SUMIF Are Not Identical This substitution only works when the target value is numeric and it is acceptable to sum any duplicates on the same key. Broken out by use case: - Unique key, numeric target: SUMIF works as a drop-in - Duplicate keys and you want the sum: SUMIF is the right tool - Target is a string: reach for INDEX and MATCH - You only want the first match: no simple SUMIF substitute For string targets, INDEX https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd combined with MATCH https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a fits the shape of the problem: INDEX with MATCH has been supported in both Excel and Google Sheets for a long time, and survives the .xlsx ↔ Google Sheets round trip cleanly. Designing for Portability For files that will be opened in both Excel and Google Sheets, centering the design on this small set of primitives keeps the sheet stable: - Conditional aggregation: SUMIF, SUMIFS - Sum: SUM - Rounding: ROUND - Branching: IF - Exact-match lookup: INDEX with MATCH - Reconciliation: compare source total and computed total with IF For example, a reconciliation cell that checks the computed total against the source total is one line: When you need the sum of line items to hit a fixed invoice total exactly rounding aside , compute the last row as the balancing difference: Reconciliation cells like these turn a silent breakage during conversion into a visible mismatch on the sheet itself. Keeping formulas simple and adding explicit reconciliation are two halves of the same practice; neither works alone. Practical Rules A function-compatibility matrix alone is not enough to predict Excel ↔ Google Sheets behavior. Even functions that both sides support can produce different results once file conversion or recalculation is inserted between them. The XLOOKUP case above is exactly that pattern. The rules of thumb I now use: - Keep aggregation formulas as simple as possible - Pin reference ranges explicitly - Separate input data and computed results into different sheets - Add reconciliation cells that check for a matching total - Actually recalculate in both .xlsx and Google Sheets - Verify the formula in the cell, not just the displayed value Rules 5 and 6 matter more, not less, when you are handing this work to an AI agent. The agent will look at Excel’s recalculated result and conclude “it’s correct,” and it takes a human or a reconciliation cell to catch the case where the Google Sheets copy silently disagrees. Wrap-Up The lesson here is not “don’t use XLOOKUP.” The point is that for a file that will live in more than one spreadsheet tool, function choice should factor in conversion, recalculation, and reconciliation, not just how convenient the newest function is. Simple functions plus explicit reconciliation cells produce sheets that survive the round trip and are easier for a third party to verify. That’s all from replacing XLOOKUP with SUMIF to make an Excel-authored file survive the Google Sheets round trip, from the Gemba.