Spreadsheets were not built for this. The user who posted this question, trying to match 14-digit alphanumeric codes against a table where some entries are shorter wildcards, has hit the exact wall that traditional tools hide until you need them most. Every digit in those codes carries meaning, and the lookup logic should be simple: if a 10-digit wildcard matches the first ten digits of a 14-digit value, the match should succeed. But spreadsheet functions like VLOOKUP and XLOOKUP treat wildcards as search patterns, not as data inside the lookup range. The table array itself cannot contain asterisks that stand in for missing digits. That is a design limitation, not a user error.
This is not a niche problem. Any system that uses hierarchical identifiers, part numbers, customer tiers, account codes, product families, runs into this exact mismatch. The user has done the right thing by searching for solutions. The frustration is not with their understanding; it is with a tool that forces them to restructure their data or write convoluted helper columns just to do what should be a direct lookup. The workarounds exist: splitting the lookup value, using array formulas, or building a helper column that checks LEFT matches. But each workaround adds complexity, increases the chance of error, and makes the spreadsheet harder for anyone else to use. The tool should adapt to the data, not the other way around.
This is where AI-native approaches change the equation. When the spreadsheet understands the intent behind the match, that a 10-digit value is a prefix wildcard, it can handle the logic without the user building a scaffolding of intermediate formulas. The user should not have to explain to their spreadsheet that a shorter code means "match the first ten characters." That should be inferred from the structure of the data itself. AI makes this possible by treating the lookup as a semantic operation, not a rigid cell-by-cell comparison. The result is a spreadsheet that works the way people think: match the meaningful part, ignore the rest.
The practical takeaway is clear. If you are managing data with tiered identifiers, and many of us are, stop accepting that your spreadsheet cannot handle wildcards in the table. The problem is not you. The problem is that legacy tools were designed for flat, predictable data. Your data is not flat, and your workflows should not have to pretend it is. Explore tools that let you describe the relationship between your values, not just the exact string. That is where the real productivity gain lives.