There's something almost poetic about a spreadsheet that works most of the time. It's the quiet hum of a machine that mostly does its job, until one Tuesday afternoon it doesn't, and you're left staring at a #N/A that makes no sense. That's exactly where the original poster found themselves after nearly a year of fighting a VLOOKUP that pulled descriptions correctly 80% of the time, while a second VLOOKUP for prices worked flawlessly every single time. Same sheet, same part numbers, same data. The difference came down to one thing: how the lookup values were being read.
The clues are all there. The working formula used `VALUE(A2)` and searched `VALUE('Price List'!$B$1:$D$15070)`, forcing both sides into true numbers. The broken one used `TEXT(A2,"0")` and looked up against `'Price List'!B1:'Price List'!C:C` with the description column as the return. That's the tell. When you convert a number to text, you're at the mercy of formatting quirks. A part number like 12345 might be stored as text in one spot, a number in another, or worse, have invisible characters that TRIM doesn't catch. The price lookup worked because it forced everything into a numeric format, bypassing those inconsistencies. The description lookup failed because it assumed consistency that wasn't there. This isn't a mystery; it's a data type mismatch dressed up as a glitch.
Here's the practical takeaway, and it's one we'd shout from the rooftops if we could: if a formula works sometimes but not others, the problem isn't the formula. It's the data. In this case, the fix is to standardize the lookup column so every part number is stored the same way. A quick check with `=ISNUMBER(B2)` on the Price List tab would reveal whether some cells are text, and a column of `=TRIM(CLEAN(B2))` can strip out non-printing characters that TRIM alone misses. The real lesson mirrors what we've seen in other user struggles, like the person wrestling with Excel not filtering unique values, where the issue wasn't the filter but hidden spaces and inconsistent data types. Same story with Power Query help spitting data from a column into multiple new column, where splitting logic breaks because of uneven delimiters. And it echoes the frustration of Problem with the Paste function in Excel 2021, where the tool isn't broken, just misread.
What we'd tell anyone in this situation is simple: stop patching the symptoms. Don't just wrap another function around the lookup. Go upstream and clean the source. Use Power Query to load the Price List, transform the part number column to a consistent type, and trim all text. That single step would have saved this person a year of headaches. The fact that XLOOKUP returned the same error only confirms it's not about the function; it's about the data underneath. So the next time you see a formula that works "most of the time," resist the urge to blame the software. Ask yourself what's different about the rows that fail. That question, not another nested formula, is what actually solves the problem. The concrete point to watch for: check if any part numbers contain leading zeros, because if they do, converting to text strips them, and that alone will break your lookup in ways you won't see coming.