There's a smarter way to do this, and it doesn't require a maze of nested IFs or a macro. The Reddit user who asked for help here has a clean, common problem: a spreadsheet with accounts, a row of Y/N criteria columns, and a lookup tab that should return only the columns marked Y in a simple vertical list. The instinct to build a lookup tab is good, but the approach usually stops short of what the tool can actually do.
What this user needs isn't more formulas, it's a better formula. The classic INDEX-MATCH or XLOOKUP will return one value per lookup. But the request here is to return multiple values (the column headers) from a single row, filtered by a condition (Y). That's a different pattern. The solution is to combine FILTER with TRANSPOSE and a conditional array. In modern Excel or Google Sheets, you can write something like `=TRANSPOSE(FILTER($B$1:$Z$1, B2:Z2="Y"))` and watch it pull every matching column header into a clean, top-down list. No helper columns, no manual concatenation, no VBA. The lookup tab stays simple, and the output is exactly what the user described: a list of criteria that are true for that account.
This matters because it reveals a deeper gap in how most people learn spreadsheets. We teach lookup functions as if the goal is always to return one piece of information, a price, a name, a date. That assumption is baked into the tools themselves. But real-world data rarely fits that mold. A single account can have multiple attributes, and the user wants to see all of them at once. The old way forces you to either repeat the lookup for each column or build a clunky workaround. The new way treats the spreadsheet like a database query: filter first, then shape the output. It's not about memorizing a new function; it's about recognizing that your data often wants to be filtered, not just looked up.
The practical takeaway is this: if you find yourself manually scanning across columns for Y's, or building a row of IF statements that only returns one match, stop. Ask whether your tool can filter a row horizontally and return multiple results. In most modern spreadsheet applications, it can. The moment you shift from "find this value" to "find all values that meet this condition," you unlock a much more natural way to work with data. The user who posted this question is on the right track, they just need one small conceptual shift to turn their lookup tab into something that actually saves time.