Why doesn't 4000 = 4000?

[SandsB]

Two files from different sources. Each has a field for the same record identifier number. Doing a VLookup and the matches aren't seen. Columns have the same format - no luck. But - If I copy/paste some of the numbers from one sheet to the other, VLookup works. I tried putting my cursor after the value and hitting enter - no luck. What's going on and what will fix this?

 

[Norie]

Have you checked they have the same data type?

It could be that in one of the sources the column you are trying to match against has numbers stored as text, or vice versa the value you are trying to look up is a number stored as text.

To check if a value that appears to be numeric you can use the ISTEXT function.

 

[SandsB]

Just did that and one file comes back True, the other False. How do I fix this?

 

[SandsB]

I added a new column, did =Right(A@,7). The ISTEXT pointed me in the right direction - thanks.
Then copied those values over the original. That worked - I don't know why it was necessary, though, Why doesn't format paint take care of the mismatch?

 

[MARK858]

Why doesn't format paint take care of the mismatch?

Formats only affect how the cell appears, they don't change the underlying value.

What you could have tried was selecting the data, Click the Data tab on the ribbon, Text to columns, Delimited, Next, check that the Other box is clear and click Finish.