The problem isn't your data. It's the mismatch between what you see and what your spreadsheet sees. When you use MID to extract characters from a string, it returns text, always. A "5" pulled from a cell is the character "5," not the number 5. Your lookup table likely stores numbers as actual numeric values. That difference breaks the match. XLookup is precise to a fault: it treats text and numbers as distinct species. Alpha and special characters happen to work because they have no numeric counterpart to confuse the issue. Your MID function is doing its job. The problem is that you are asking it to speak a different language than your lookup table.
This is a common friction point in spreadsheet work, and it exposes a deeper truth about how tools like Excel and Google Sheets handle data types. They are strict interpreters, not flexible readers. A number stored as text looks identical to a number stored as a value, but the engine refuses to see them as the same. The solution is straightforward: wrap your MID result in the VALUE function, or multiply the result by 1 to force a numeric conversion. Alternatively, store your lookup table's numeric keys as text by formatting them as such. Either approach aligns the data types and lets XLookup do its work.
What this really means for you is a lesson in data hygiene. Spreadsheets reward consistency. When you mix types, some cells formatted as text, others as numbers, you invite invisible errors that erode trust in your results. The mistake here is not a flaw in XLookup or MID. It is a reminder that every function makes assumptions about what it receives. Your job is to make those assumptions explicit. Check your data types. Use ISTEXT or ISNUMBER to verify. And when you build a lookup table, decide upfront whether its keys will be text or numbers, then stick with that decision across every reference.
The next time you run into this, you will know exactly where to look. That is the point of understanding, not just fixing. You do not need a smarter tool. You need a clearer view of how the tool sees your data.