This user's frustration is familiar to anyone who has tried to force traditional spreadsheet formulas to do cross-sheet work. The formula they attempted, nesting XLOOKUP inside SUM with a range reference, is a reasonable guess, but it fails because XLOOKUP returns a single cell, not a range, and because formula-based percentages in Sheet2 introduce calculation dependencies that break the lookup chain. The real issue isn't a missing parenthesis or a typo. It's that the tool itself wasn't designed for this kind of task.
The better way is not a longer formula. It is an AI-native approach that treats data as connected, not isolated. Instead of manually constructing a lookup-and-sum pattern that fights the grid, you can define a relationship between the two sheets, matching on the Name field, and then ask the tool to sum the matching percentages. The AI handles the cross-sheet logic, the formula references, and the aggregation in one step. No nested functions. No cell-range gymnastics. No debugging a #VALUE error that offers no clue about what went wrong.
What this means for you is a shift in how you think about spreadsheets. The old model assumes you are the programmer of every cell. The new model assumes you are the director of the data. You describe what you need: "Find the matching names across these two sheets and add up their percentages." The AI executes the logic. The error messages disappear because the underlying engine understands the structure of your data, not just the syntax of your formula.
We believe this is the direction spreadsheet tools must take. Users should not have to master arcane function combinations to do something as straightforward as summing matched values across sheets. The technology exists to make this intuitive. The question is whether the tools you are using today will deliver it. If you are spending time debugging cross-sheet lookups, consider whether the tool is helping you or getting in your way. The next step is to try a tool that treats your question as a request, not a puzzle.