I am using Excel 2023 (version 2302). Its cell value formatting is messing with vlookup.
My numbers come from a CSV file that I imported as US-ASCII, they are parcel numbers such as 100011149032, I am trying to vlookup it in another target range that contains sale record, it is definitely in that range in TEXT format. Now, if I put an apostrophe (') in front that parcel number, it will find it, but if I don't, no matter what cell format I tried, it couldn't find it. I've tried format it as Text, or number with no decimal point etc, none works.
Also, when I format it to be TEXT, it automatically displays in scientific notation, I had to force custom format to display the whole number.
I got it to work by first opening an empty Excel spreadsheet, then pre-format the data range cells to be "Text" format, then open the data file that are imported as US-ASCII and copy-n-paste into the preformatted data range. This way, all of the parcel IDs are reliably formatted in one common format and the VLOOKUP starts working correctly. Granted this is a bit of a workaround, still hoping to have a more systematic and consistent way of dealing with Excel "overtly" doing its own formatting under the hood.