0

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.

For Comment
  • 1,139
  • 4
  • 13
  • 25
  • Are you aware that merely changing the **format** of the cell from `text` to `general` or `number` does not change the data type being stored in that cell? That is where your problem likely is. You need to ensure that the search value in `VLOOKUP` is of the same data type as in your lookup_range. And you cannot rely on the format of the cell to tell you the format of the value in the cell. – Ron Rosenfeld Mar 29 '23 at 00:45

1 Answers1

0

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.

For Comment
  • 1,139
  • 4
  • 13
  • 25