This user has hit the wall that every inventory manager eventually reaches. The VLOOKUP worked for product names, but the real challenge, pulling location-specific quantities from a vertical list into a horizontal layout, exposes the limits of traditional spreadsheet logic. The problem isn't the user's skill; it's the tool. Spreadsheets were never designed to handle this kind of relational data gracefully, especially when locations like "EVENTS" appear unpredictably and the table structure shifts beneath your formulas.
What this user needs is not a more complex formula, but a fundamentally different approach to data. The vertical layout in Sheet 1 is actually the correct, normalized structure for inventory records: one row per product per location, with the quantity as a single value. That format is built for accurate storage and flexible querying. The horizontal view on Sheet 2 is the display format, what humans prefer for reading and comparing. The gap between these two structures is where frustration lives. VLOOKUP and INDEX/MATCH can bridge it, but only when the data is perfectly consistent and you know exactly where to look. When locations are irregular, as they are here, those functions break down. The user is left hand-copying or building fragile nested formulas that break with the next update.
Our take is plain: stop fighting the spreadsheet's structure and let the software do the transformation. Modern AI-native spreadsheet tools can read the vertical inventory table, understand that "Shop Name" and "Qty" are paired fields under each product, and pivot that data into the horizontal layout automatically, without the user writing a single lookup formula. The tool handles the irregular locations, the variable number of shops per product, and the need to keep the output current as inventory changes. This isn't about replacing the user's effort; it's about redirecting it toward analysis and decisions rather than data wrangling.
The user deserves a tool that respects their time. When a spreadsheet requires color-coded explanations and forum posts just to describe the problem, the tool has failed. The solution is to adopt a spreadsheet that treats data as structured information, not as a grid of cells. That shift transforms a headache into a simple command: "pivot this vertical list into a horizontal view." No VLOOKUP, no manual matching, no frustration. The user's goal, get inventory numbers into the right layout, should be the starting point, not the finish line.