This user's problem is a perfect example of why traditional spreadsheets are no longer the right tool for modern data management. The request is straightforward: find students who have completed specific prerequisite classes, check their progress percentage in another column, and output their names into a new table. But the user is stuck, not because the logic is difficult, but because Excel forces them to fight against its own structure. That's not a user failure. It's a tool failure.
The user needs to check column H for class names, verify column I for completion status, then cross-reference column G for a percentage between 0% and 100%. If all conditions are met, the student's name from column B should appear in a separate table. In Excel, this requires nested IF statements, array formulas, or a combination of INDEX/MATCH and FILTER, assuming the data stays static and unfiltered. The moment columns are reorganized or filters applied, those formulas break. The user explicitly notes this: "The formula needs to work regardless of how the columns are organized/filtered." That sentence alone should signal that the spreadsheet model has reached its limit.
What this user really needs is a system where data relationships are defined by meaning, not by cell coordinates. In an AI-native spreadsheet, you would simply describe the logic: "Show me students who completed prerequisite X and have a progress percentage between 1% and 99%." The tool would understand that "prerequisites" are a set of conditions tied to student records, not a fragile reference to H2:I50. It would maintain those relationships even when columns are moved, filtered, or renamed. The user wouldn't have to worry about absolute vs. relative references, volatile functions, or broken formulas after a colleague sorts a column.
The practical takeaway here is that this user's workflow is not unusual. It's the daily reality for anyone managing student eligibility, inventory thresholds, or project dependencies in a spreadsheet. But each conditional check, each cross-reference, each output table adds another layer of fragility. The user is asking for a formula that works "regardless of how the columns are organized." That request is reasonable. The only unreasonable part is expecting a legacy spreadsheet to deliver it. The solution is not a better formula. It's a better foundation, one where the data model, not the cell grid, drives the results.