Trouble with vlookup and numbers stored as text

[SantaClaus]

Hi all,

I often have to lookup data based on fields which can contain both
text and numbers. However, some of the numbers are sometimes stored as
text, and some other times as numbers, in which case an error is
returned.
The only solution I found is to create two new fields, one in the
source table and one in the target one, with the formula:
=if(iserror(IndexColumn*1,IndexColumn,IndexColumn*1)

where IndexColumn is the field the lookups are based on. This works,
but is extremely annoying and cumbersome to implement when you're
performing several lookups based on different fields.

Is there a better solution?

I appreciate Excel is not a database (even though most people use it
as one, causing all sorts of trouble...) , but I would have expected
it to handle such a banal task a little better...

Thanks!

 

[Don Guillett Excel MVP]

Hi all,

I often have to lookup data based on fields which can contain both
text and numbers. However, some of the numbers are sometimes stored as
text, and some other times as numbers, in which case an error is
returned.
The only solution I found is to create two new fields, one in the
source table and one in the target one, with the formula:
=if(iserror(IndexColumn*1,IndexColumn,IndexColumn*1)

where IndexColumn is the field the lookups are based on. This works,
but is extremely annoying and cumbersome to implement when you're
performing several lookups based on different fields.

Is there a better solution?

I appreciate Excel is not a database (even though most people use it
as one, causing all sorts of trouble...) , but I would have expected
it to handle such a banal task a little better...

Thanks!

Convert all to numbers or try this idea.
=IF(ISNA(VLOOKUP(TEXT(I1,"#,###"),G3:H9,2,0)),VLOOKUP(I1,G3:H9,2,0),VLOOKUP(TEXT(I1,"#,###"),G3:H9,2,0))

 

[SantaClaus]

Convert all to numbers or try this idea.
=IF(ISNA(VLOOKUP(TEXT(I1,"#,###"),G3:H9,2,0)),VLOOKUP(I1,G3:H9,2,0),VLOOKUP(TEXT(I1,"#,###"),G3:H9,2,0))

Thanks, I'll give it a try. It's very annoying that Excel cannot even
handle such a banal task without forcing the user to resort to these
messy work arounds... Instead of wasting time on pointless eye candy
Microsoft should think of this serious stuff instead!

Databases are so much more powerful: a SQL join is much more
straightforward and elegant!

 

[Matt Richardson]

Thanks, I'll give it a try. It's very annoying that Excel cannot even
handle such a banal task without forcing the user to resort to these
messy work arounds... Instead of wasting time on pointless eye candy
Microsoft should think of this serious stuff instead!

Databases are so much more powerful: a SQL join is much more
straightforward and elegant!

Another option is to use the double unary operator (--) when carrying
out your VLOOKUPS. This will convert numbers formatted as text into a
number format.

More info on use of double unary in VLOOKUPs can be found here (Look
out for the Troubleshoot the Excel VLOOKUP formula bit near the
bottom):-

http://www.contextures.com/xlfunctions02.html#Trouble

I also wrote a brief article on this some time back:-

http://teachr.blogspot.com/2006/08/excel-quick-hint-converting-text-to.html

HTH
Matt
http://2toria.com
http://teachr.blogspot.com