Vlookups yielding wrong N/A results (numbers stored as texts andviceversa)

[myemail.an]

Hi there,

my problem is that I have to do a number of vlookups (exact matches)
on customer codes (which are numeric values). Most of the times, Excel
returns an N/A error, even when it shouldn't, because the code is in
both tables. My understanding is that this happens when Excel treats
the code in the first table as a number, and the one in the second
table as a text.

How do I solve this extremely annoying problem?

Since Excel is a spreadsheet and not a database, it allows you to
choose a formatting, but not to set a data type for a field. So how do
I make sure Excel treats the customer codes consistently in both
tables?

This is one of the many reasons why I hate Excel and try to do as much
work as I can with Access or, better yet, with SQL. unfortunately,
there are times when I just have to use Excel...

Thanks!

 

[Gord Dibben]

Once the data is in Excel, the easiest way to change to numbers is to format all
as General.

Copy and empty cell.

Select the data range and Edit>Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

It's up to you to make sure your data is what it should be--numbers treated as
text or treated as numbers.

You can preformat cells as Text and do your data entry and the values in those
cells will be text--even if they look like numbers.

Or you can prefix every entry with an apostrophe: '1234
to force excel to treat that entry as text.

But you may have noticed that just changing the format of a cell from Text to
General (or vice versa) doesn't affect the value in that cell.

If you're positive that the first column of the lookup table is text, then you
can modify your =vlookup() formula:

=vlookup(a1&"", sheet2!a:c, 3, false)

If A1 holds a real number, then A1&"" will be text.

If the first column of the lookup table is really numeric, then you could
convert the "text" value to number with a formula like:

=vlookup(--a1, sheet2!a:c, 3, false)

But personally, I've always found it much better to fix the data. Then I don't
have to do any adjustments.

I like to use a helper column to convert Numbers to text:
=A1&""
or
=text(A1,"00000")
(if I needed 5 digits with leading 0's)

And if I have to convert text numbers to number numbers, then I'd use this:
Select an empty cell
edit|copy
select the range to fix
Edit|Paste special|check Add and Values

(Using xl2003 menus)

Debra Dalgleish has some more notes on trouble shooting these kinds of formulas:
http://www.contextures.com/xlFunctions02.html#Trouble

 

[myemail.an]

Thanks for your tip.

I tried, but it only solves the problem on some, not all the records.

I tried multiplying by one, and it seems to work. But it's just
absurd... To me it's the proof that Excel sucks at data analysis and
that this stuff had better be done with databases.... Why does
something that takes few lines of codes and a few seconds to do in SQL
must require so many more steps in Excel?

 

[myemail.an]

It's up to you to make sure your data is what it should be--numbers treated as
text or treated as numbers.

Absolutely. The problem is: how do I achieve this if Excel does not
let me set and enforce data types for the fields, the way I would do
in any database? I can change the formatting, but that would only
change the way the content is displayed, not the content itself.

You can preformat cells as Text and do your data entry and the values in those
cells will be text--even if they look like numbers.

I never do manual data entry myself, nor do I have any intention to.
I usually receive my files from other people and/or retrieve them from
the company's IT systems.
Let's say I have an Excel spreadsheet with two worksheets, each
containing customer codes. Let's say code 123 is present in both. How
do I know whether that 123 is treated by Excel as text or number? In a
database, I would know the data type of the field, but not in Excel!

[...]
And if I have to convert text numbers to number numbers, then I'd use this:
Select an empty cell
edit|copy
select the range to fix
Edit|Paste special|check Add and Values

(Using xl2003 menus)

Your tips are very helpful, but everything seems to suggest that, as I
always thought, Excel is a very poor tool for data analysis; for this
type of stuff, databases are much faster, reliable, and it's way way
more straightforward to audit the whole process: if I type a few lines
of SQL code, I know beyond any doubt what I did and what I didn't,
whilst, if I open an Excel file I created a while ago, or that someone
else created, I can never be 100% sure that the data cleansing was
done correctly on all columns, all rows, etc. Would you agree with
this opinion or is there something about spreadsheets vs databases I'm
just missing?

Debra Dalgleish has some more notes on trouble shooting these kinds of >ormulas:http://www.contextures.com/xlFunctions02.html#Trouble

Thanks a lot!
PS don't get me wrong: I'm mad at Excel, certainly not at the people
who are trying to help me in this forum!

 

[Dave Peterson]

You can complain about how excel allows the user to have complete control over
the value in each cell. It's not a database program.

I offered a few ways to do the data entry and to convert text to numbers and
numbers to text. I don't understand your question about how you can achieve
this.

If the people who send you the file aren't careful how they do the data entry,
then you'll have to fix the data yourself--well, if it's important to you.

You can use formulas to return the quantity of entries in a range:
=counta(a:a)
and another formula to count the quantities of numbers:
=count(a:a)

If they don't match, then you know you have some non-numeric entries.

On an individual cell basis, you can use:
=isnumber(a1)
and
=istext(a1)

I know if I find using some other tools more effective, then I'd use some other
tool.

It's up to you to make sure your data is what it should be--numbers treated as
text or treated as numbers.

Absolutely. The problem is: how do I achieve this if Excel does not
let me set and enforce data types for the fields, the way I would do
in any database? I can change the formatting, but that would only
change the way the content is displayed, not the content itself.

You can preformat cells as Text and do your data entry and the values in those
cells will be text--even if they look like numbers.

I never do manual data entry myself, nor do I have any intention to.
I usually receive my files from other people and/or retrieve them from
the company's IT systems.
Let's say I have an Excel spreadsheet with two worksheets, each
containing customer codes. Let's say code 123 is present in both. How
do I know whether that 123 is treated by Excel as text or number? In a
database, I would know the data type of the field, but not in Excel!

[...]
And if I have to convert text numbers to number numbers, then I'd use this:
Select an empty cell
edit|copy
select the range to fix
Edit|Paste special|check Add and Values

(Using xl2003 menus)

Your tips are very helpful, but everything seems to suggest that, as I
always thought, Excel is a very poor tool for data analysis; for this
type of stuff, databases are much faster, reliable, and it's way way
more straightforward to audit the whole process: if I type a few lines
of SQL code, I know beyond any doubt what I did and what I didn't,
whilst, if I open an Excel file I created a while ago, or that someone
else created, I can never be 100% sure that the data cleansing was
done correctly on all columns, all rows, etc. Would you agree with
this opinion or is there something about spreadsheets vs databases I'm
just missing?

Debra Dalgleish has some more notes on trouble shooting these kinds of >ormulas:http://www.contextures.com/xlFunctions02.html#Trouble

Thanks a lot!
PS don't get me wrong: I'm mad at Excel, certainly not at the people
who are trying to help me in this forum!

 

[Gord Dibben]

If multiplying by 1 works then the Add should also work.

My guess is the copied blank cell was not formatted as General.

On the other parts of your rant.................Yep, you're right,

A number-crunching application is not the best tool for a database.


Gord