There's a moment in every spreadsheet user's life when the tool stops feeling like a partner and starts feeling like a locked door. This moment is captured perfectly: 120,000 rows, a drop-down that works for most entries but not all, and a filter that refuses to surface the very cells you need. The instinct is to blame the validation, the formula, or yourself. But the real issue here isn't a broken feature, it's a misunderstanding of how data hides in plain sight.
When auto-complete works for many options but not others, and the filter on the source worksheet also misses those same "problem cells," something structural is at play. It's not random glitchiness. It's likely that those rows live outside the defined range your drop-down references, or they contain formatting, hidden characters, or data types that break the matching logic. The fact that Get & Transform also fails points to the same root cause: the source itself is inconsistent, not the tooling. For you, the practical takeaway is this: before you rebuild your validation or abandon the worksheet altogether, audit the raw data. Check for trailing spaces, non-printing characters, or values stored as text instead of numbers. Those invisible culprits are almost always the reason a filter says "no" when you know the answer is yes.
What makes this story worth pausing on is not the technical fix, though that matters, but what it reveals about how we work. Most spreadsheet users don't have a mental model for why data behaves inconsistently at scale. They assume that if a cell looks right, it is right. But at 120,000 rows, the margin for invisible error compounds. The user here did exactly what we'd hope: they tried the obvious solutions, then escalated to a more powerful tool. That's not a failure. That's a workflow. The problem is that the workflow stops at the tool, when it should start with the data's integrity.
So here's our plain take: your drop-down isn't broken, and neither are you. The data is lying to you in subtle ways, and until you treat the source as the suspect, you'll keep chasing symptoms. Run a quick diagnostic. Use a helper column with `LEN()` and `TRIM()` to spot anomalies. Filter for blanks in adjacent columns. Convert the entire range to a table so your validation references a dynamic, named structure instead of a static address. And if Get & Transform still won't cooperate, check the query's data type inference, it may be silently coercing values into formats that don't match your list. The solution isn't a bigger hammer; it's a cleaner foundation. Once you find those hidden rows, you won't just fix the drop-down. You'll understand your data in a way that makes the next 120,000 rows less intimidating.