# Choosing Portable Functions for Spreadsheets Shared Between Excel and Google Sheets — Replacing XLOOKUP with SUMIF for Compatibility > Source: > Published: 2026-08-05 14:58:23+00:00 # 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.