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

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

"Josh" wrote:

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

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

"Mike" wrote:

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

"Josh" wrote:

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

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 datatext to columnsfixed width and putting the
break after the last digit in the invoice number.

HTH


"Josh" wrote:

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

"Mike" wrote:

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

"Josh" wrote:

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

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

"pablo bellissimo" wrote:

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 datatext to columnsfixed width and putting the
break after the last digit in the invoice number.

HTH


"Josh" wrote:

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

"Mike" wrote:

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

"Josh" wrote:

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

Thank you very much pablo.
I solved my problem by:

If there
is and apostrophe, try using datatext to columnsfixed width and putting the
break after the last digit in the invoice number.

thank you once again

It will not be in the results of the LEN function that is precisely to force the format rather than being a true content of the excell. Data Text to Columns fixed width and put the pause after the last digit of the invoice. It is very best to get a data.