Most spreadsheet problems aren't really about the formulas. They're about the structure of the data that someone else decided was good enough. The user who posted this knows exactly what they need: a way to take Sage's messy, multi-code export and have a formula reliably assign the correct nominal code to every row below it until a new code appears. That is a perfectly reasonable request, and it reveals a deeper truth about how most finance teams actually work. The real skill isn't building a solution for yourself, it's building one that survives contact with the rest of the office.
The core technical challenge here is straightforward. Sage exports often place a nominal code once at the top of a block of transactions, and every row below inherits that code until a new block starts. Excel's native lookup functions struggle with this pattern because they want a clean, one-to-one relationship. The solution is to use a formula that checks whether the current row has a nominal code; if it does, keep it; if it doesn't, look up to the nearest row above that does. An `IF` combined with `INDEX` and `MATCH` or, more elegantly, a `SCAN` function in newer Excel versions, can achieve this without a single macro. The user's instinct to avoid macros for team-wide distribution is exactly right, macros create trust issues and security warnings that kill adoption. A formula-based approach lets anyone copy and paste raw data into a sheet and watch the output populate automatically.
What this user has done is build a cleaner that the rest of the office can use without needing to understand how it works. That is the definition of good spreadsheet design. They have prioritized maintainability over elegance, and that is the right trade-off. Power Query would be technically superior, but it requires every user to know how to refresh a query or navigate the Power Query editor. A macro is powerful, but it introduces fear. A well-constructed formula sheet, however, is invisible. It just works. The user has correctly identified that the goal is not the most technically impressive solution, it is the one that will actually be used by people who do not want to think about spreadsheets.
This is where the future of data work lives. Not in building one-off solutions that only the builder can maintain, but in creating systems that empower others to do their jobs without friction. The user's frustration is not with Sage or Excel, it is with the gap between what tools promise and what teams actually need. Our take is simple: the best formula is the one that disappears. The user has already found the right question. Now they just need the right formula to finish the job.