This user has built a working solution, but they can feel the friction. They have a filter that works when the criteria are hardcoded, and they know that approach will not scale across eight categories and multiple sheets. Their instinct to move the criteria into a table is the right one. The problem is that spreadsheets have trained us to think in exact matches, and real-world data rarely cooperates. When a location field reads "US-TX-Beaumont" and your lookup table only contains "US-TX," the traditional approach fails. The user is asking for a wildcard, and they should not have to fight the tool to get it.
What this user has described is not a niche edge case. It is the daily reality for anyone managing regional data across multiple offices, service areas, or territories. The manual approach, writing out every possible prefix in a static array, works for three locations but breaks at thirteen. It becomes unreadable, unmaintainable, and prone to error. The user has already identified the more elegant path: a lookup table that acts as a reference, not a rigid list. The challenge is that most spreadsheet formulas are built for exact comparisons, not partial matches against a set of values. The SEARCH function can do the partial matching, but combining it with a table reference requires a different structural approach than a simple equality check.
The solution here is to rethink how the lookup table interacts with the filter. Instead of trying to match the full location string against the table, the user can use SEARCH to check if any value in the lookup table appears within the location string. The BYROW approach they attempted is close, but the syntax needs adjustment. A working pattern is: `FILTER(sheet!A:L, BYROW(SEARCH(Table1[NEUS], sheet!J:J), LAMBDA(r, SUM(ISNUMBER(r))>0)))`. This returns a row if any of the lookup values appear as a substring in the location. For the multi-category problem, a single lookup table with a category column eliminates the need for eight separate formulas. One FILTER, one reference table, and the user can generate all their regional sheets from a single source.
This is what a modern data workflow should feel like. The user should not have to choose between readability and functionality. By moving the criteria into a table and using SEARCH with BYROW, they get both a maintainable system and a dynamic one. When a new location code needs to be added to a category, they edit the table, not the formula. That is the upgrade worth making.