vlookup and #n/a due to formatting
 

We are moving to a new accounting system. I'm attempting to test the quality
of the uploaded data making sure the new account codes have been mapped
correctly.

We have a table mapping the old chart of account segments to the new. In
the old system, all segments are built into a single, 12 digit expression. I
use left()
function to isolate the segments in the old chart of accounts. This is the
first section of vlookup. The mapping table is the second section. This
always gives me #n/a as the result.

If I highlight the mapping table and press F2-enter on each cell, the #n/a
goes away. However, when I compare the result to the data dump from the new
system, I continue to some sort of formatting issue. This issue goes away if
I do a copy paste-value of the column derived using the left() function, but
only after going through the F2-enter routine on each of the hundreds of
cells.

There has to be a better approach. I've tried changing everything to text,
to general, whatever. Nothing works except to stress my carpal tunnel
syndrome.

Example:

Old Parsed New
1000-000-000 1000 10100

What had started with 1000 in my old system needs to start with 10100 in my
new. The mapping table ties 1000 to 10100. The left() function returns 1000
for any records starting with 1000. The Vlookup function is supposed to take
the Parsed number resulting from use of the left() function and return the
New number. But for #n/a, it works as advertised.

Any ideas? Thanks.

vlookup and #n/a due to formatting
 

Hi,

The LEFT function converts the result to TEXT you could use

=--LEFT(A1,4)

instead.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"rhoneyman" wrote:

We are moving to a new accounting system. I'm attempting to test the quality
of the uploaded data making sure the new account codes have been mapped
correctly.

We have a table mapping the old chart of account segments to the new. In
the old system, all segments are built into a single, 12 digit expression. I
use left()
function to isolate the segments in the old chart of accounts. This is the
first section of vlookup. The mapping table is the second section. This
always gives me #n/a as the result.

If I highlight the mapping table and press F2-enter on each cell, the #n/a
goes away. However, when I compare the result to the data dump from the new
system, I continue to some sort of formatting issue. This issue goes away if
I do a copy paste-value of the column derived using the left() function, but
only after going through the F2-enter routine on each of the hundreds of
cells.

There has to be a better approach. I've tried changing everything to text,
to general, whatever. Nothing works except to stress my carpal tunnel
syndrome.

Example:

Old Parsed New
1000-000-000 1000 10100

What had started with 1000 in my old system needs to start with 10100 in my
new. The mapping table ties 1000 to 10100. The left() function returns 1000
for any records starting with 1000. The Vlookup function is supposed to take
the Parsed number resulting from use of the left() function and return the
New number. But for #n/a, it works as advertised.

Any ideas? Thanks.

vlookup and #n/a due to formatting
 

LEFT returns a *TEXT* string even if it's numbers.

You need to coerce these text numbers back to numeric numbers.

Try one of these. They all do the same thing:

=--LEFT(A1,12)
=LEFT(A1,12)+0
=LEFT(A1,12)*1
=VALUE(LEFT(A1,5))

--
Biff
Microsoft Excel MVP


"rhoneyman" wrote in message
...
We are moving to a new accounting system. I'm attempting to test the
quality
of the uploaded data making sure the new account codes have been mapped
correctly.

We have a table mapping the old chart of account segments to the new. In
the old system, all segments are built into a single, 12 digit expression.
I
use left()
function to isolate the segments in the old chart of accounts. This is
the
first section of vlookup. The mapping table is the second section. This
always gives me #n/a as the result.

If I highlight the mapping table and press F2-enter on each cell, the #n/a
goes away. However, when I compare the result to the data dump from the
new
system, I continue to some sort of formatting issue. This issue goes away
if
I do a copy paste-value of the column derived using the left() function,
but
only after going through the F2-enter routine on each of the hundreds of
cells.

There has to be a better approach. I've tried changing everything to
text,
to general, whatever. Nothing works except to stress my carpal tunnel
syndrome.

Example:

Old Parsed New
1000-000-000 1000 10100

What had started with 1000 in my old system needs to start with 10100 in
my
new. The mapping table ties 1000 to 10100. The left() function returns
1000
for any records starting with 1000. The Vlookup function is supposed to
take
the Parsed number resulting from use of the left() function and return the
New number. But for #n/a, it works as advertised.

Any ideas? Thanks.