Merged cells are a familiar pain for anyone who works with real-world data, especially when that data comes from a system that values visual layout over functional structure. The formula shared here is a clever workaround, using SCAN to backfill property names across merged rows and XLOOKUP to target resident records. It works, but only for one column. That is not a solution; it is a stopgap that leaves half the story on the table. Our view is clear: you should not have to rebuild your spreadsheet every time the source data changes shape. The real fix here is not a longer formula, it is a smarter approach to extraction.
The core challenge is that the data is organized for human reading, not machine processing. Column A holds merged property names that repeat invisibly across rows. Column B does the same for resident categories. Then columns C and D behave like normal unmerged cells, creating a hybrid structure that standard lookup functions struggle to navigate. The user's current formula successfully retrieves rent from column D, but when they ask "How do I also pull resident other?" the logical next step is to add another lookup for column C, or to restructure the source. But adding a second XLOOKUP that reads a different column C value while keeping the same row conditions introduces fragility. If column C labels ever change slightly, or if merged rows shift, the whole thing breaks. That is not maintainable.
A more durable solution involves unpivoting the merged data before any lookup occurs. Instead of fighting the merged cells, treat them as metadata that should be expanded into every row. A helper column using SCAN (which they already use) can fill the property name down, and a second SCAN on column B can fill the resident label down. Once every row has its own explicit property name and resident category, you can use a simple SUMIFS or FILTER to pull rent and other totals in one pass. This adds two columns of helper data, but it eliminates all future guesswork. The formula becomes something like: =FILTER('Insert Collections'!$D$12:$D$9999, (HelperA = [@[RealPage name]]) * (HelperB = "RESIDENT") * ('Insert Collections'!$C$12:$C$9999 = "Rent") ) And a second FILTER for "Other." No merged cells, no ambiguous lookups, no manual fixes when new data arrives.
The real takeaway here is that spreadsheets were never designed for this kind of nested, merged data. The user is solving a problem that the tool itself created. Rather than writing longer formulas to compensate, the smarter move is to reshape the data into a flat, consistent structure that formulas can navigate cleanly. That shift, from working around the data to cleaning it first, is the difference between a usable spreadsheet and a recurring headache. Start by unmerging and filling down, then let the formulas do what they do best: simple, precise lookups.