VLook Up issue

  • Thread starter Thread starter Stav19
  • Start date Start date
S

Stav19

Hi All

hopefully a straightforward question...I'm using the following kind of
lookup in a work book, looking up a value between two sheets.

IF(ISERROR(VLOOKUP(.....),"0",VLOOKUP(...)))

The problem isn't the formula, it's that when I copy and paste value
the range to look up in, I have to literally go into each cell, press
F2 and enter till the end of the range of data for the formula to
work, otherwise I just get a 0.

I've tried changing the formatting of the cells etc, also tried using
"Trim" and substitute, both to no joy, any help would be appreciated!

Cheers
 
Your copy and paste may be returning text.

From where do you copy?

Once pasted, format all to General then copy an empty cell.

Select the range of pasted values and Edit>Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP
 
Maybe you don't have excel in autocalculate mode.

In xl2003 menus, it's under:
tools|options|calculation tab

ps. I would think you'd want a real zero returned--not a text "0":
=IF(ISERROR(VLOOKUP(.....),0,VLOOKUP(...)))

pps. You may find that looking for an #N/A error is better than hiding all
errors:
=IF(ISna(VLOOKUP(.....),0,VLOOKUP(...)))
 
Hi,

First, you can simplify the formula you are using to read:

IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(...))

or in 2007

=IFERROR(VLOOKUP(.....),0)

Now back to your original question:

The Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being entered
as text. Numbers can be stored as text by 1. preformatting the cell to Text
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
number, 4. Because you used the Text to Columns command and converted it to
text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles may
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers.
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.
Cheers,
Shane Devenshire
 
Hi,

First, you can simplify the formula you are using to read:

IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(...))

or in 2007

=IFERROR(VLOOKUP(.....),0)

Now back to your original question:

The Problem:
When numbers are enter as text they may not calculate within formulas as
they should.  A few formulas will work fine despite the numbers being entered
as text.  Numbers can be stored as text by 1. preformatting the cell toText
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
number, 4. Because you used the Text to Columns command and converted it to
text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case..  
If you are using a later version of Excel, Error Checking green trianglesmay
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions.  You can not tell by checking the Format.  If a
number was entered in a cell preformatted as General or as a number, thenit
will be a number, even if it's current format is Text.  Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers.
3. Select an empty cell and copy it.  Select the text number cell and choose
Edit, Paste Special, Add (or Subtract).  This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.
Cheers,
Shane Devenshire








- Show quoted text -
Click to expand...

cheers for all the responses guys. Thanks for the explanation Shane,
very useful, and the third option was definitely the quickest, I
thought that I could change the format using a formula, but this
method worked just as well!
thanks again!
 
Back
Top