Hi,

Such a time! Since the number in the first column are in order 1, A1+1,...

the VLOOKUP is not failing because of the last argurment. For exact matches

the sort order is not important, for approximate matches the sort must by

ascending on the lookup column, but your is.

So the most likely problem is that the random numbers are not numbers, they

are text or the numbers in column A are text, less likely. This problem and

its solutions are discussed below:

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.