Same value in two cells are not the same??

[Sweedler]

Hello

So I have 2 separate dumps of information including book titles and numbers. I use a VLOOKUP to compile information on one of the sheets by gathering info that only exists on the other sheet. Even though I can clearly see that both numbers (as it is the numbers that I am using to search for) are the same, VLOOKUP does not find it. To make sure that they are the same I even did a simple subtraction formula involving both cells and it returns a value of zero, so they really are the same.

Why oh why oh why is it then that VLOOKUP claims the numbers are not the same.

It seems to only occur when the number is depicted as 9.78914E+12 instead of as 9789144127408. But I can't seem to alter the formatting


Please helpl

 

[mikerickson]

"Can't change the formatting" sounds like one of the numbers is actually a string.

 

[Sweedler]

"Can't change the formatting" sounds like one of the numbers is actually a string.

How do you make a string into a number .. or just general. You know, something you can work with.

 

[Joe4]

Performing mathematical computations (like addition or subtraction) on numbers entered as strings will coerce the result to be negative.
So subtracting them is not a good indicator of whether or not they are the same.

A better check is to see if they are equal, i.e. if your values were in cells A1 and B1, use a formula like:

=A1=B1

If they are the same, that formula will return TRUE. If it returns FALSE, they are not the same.

To find out if they are different formats, you can use the ISNUMBER formula on both of them, i.e.

=ISNUMBER(A1)
=ISNUMBER(B1)

Any values that return TRUE are numbers, those that return FALSE are not.

To coerce a number entered as a string to a number, you can simply perform some mathematical computation that won't change its value, like adding 0, i.e.

=A1+0