VLookup not finding values unless lookup value is manually entered

[Guest]

I have a worksheet that was downloaded from the web that I am trying to
compare with another worksheet's data. The value I am trying to compare is
the open balance by matching the invoice numbers. The vlookup formula returns
#N/A for every instance unless I manually type in the invoice number in the
worksheet that was downloaded from the web. When I manually type the invoice
number in, it returns the correct value. I have formatted the invoice number
in both worksheets as Number, but it still doesn't help unless I manually
type the invoice number.

My formula is =VLOOKUP(C2,'Sable open items'!F:J,5,FALSE).

Thanks

 

[Guest]

It sounds like the number has some erroneous spaces in and if it was
formatted as text originally then changing to format to number won't get rid
of them. To test this theory thpe =len(J1) to see how many characters are in
the number cell and if there are more than expected then the extra will be
spaces that can be removed with =Trim()

Mike

 

[Guest]

The characters in the len function is the same as the number of visible
characters in the invoice number. There aren't any extra spaces/characters in
the value.

Thanks

 

[Guest]

Josh, is there an apostrophe at the begining of the number you are trying to
lookup? This will not be included in the results of the LEN function as it
is just forcing the format rather than being a true cell content. If there
is and apostrophe, try using data>text to columns>fixed width and putting the
break after the last digit in the invoice number.

HTH

 

[Guest]

Thank you Pablo. That corrected the problem and reformatted all the invoice
numbers.