#N/A error with VLOOKUP function

[Leslie]

I have the above problem, but only very intermittently, with a VLOOKUP table
that ranks 32 percentages from highest (1) to lowest (32). It happened to me
yesterday for the first time in a long time; hence this message. The error
appeared instead of the rank 7, but I haven't kept any records to see
whether that's always the rank for which the error appears.

Before posting this, I searched the newsgroup postings on #N/A error and
VLOOKUP and saw that the suggestion was being made to numerous people to
check that the function wasn't being asked to operate on text as well as
numbers. I therefore checked that. Mine wasn't, but, just to be sure, I
reformatted every column in the worksheet, making sure that they contained
no cell formatted as text.

I also tried "trace error". That drew a blue line from the percentage that
wasn't ranked through the #N/A error and then up to the first entry in the
column next to the column where the rank should have appeared. That first
entry is the smallest percentage (32). I don't know enough to know whether
it's significant that it went to the first entry.

I also used the evaluate formula button. I printed out the screen for each
step, but don't know enough to tell the significance of what it told me.

I'd be grateful for any suggestions as to how to end the problem.

 

[Frank Kabel]

Hi
maybe it's a rounding error. Try rounding the lookup value. In addition
please post your exact formula and the example values that won't match

 

[Leslie]

Hi, Frank. I'm so ignorant I don't really know what it you're asking me to
post. Is this it?

B32 contains a number held of a particular type of share (930).
C32 contains the total purchase price of those shares ($1860.00)
H32 contains the latest price for one of those shares ($6.58).
I32 contains the formula =$B$32*$H$32, thus giving the latest total value of
the shares ($6119.40).
N32 contains the formula =I32/C32, thus giving the percentage of the total
purchase price of the shares represented by their latest total value (329%).
O32 contains the formula =VLOOKUP(N32,range3,2,FALSE) and should return the
rank which 329% bears among 31 other ranks all obtained in exactly the same
way as N32.
Range 3 is defined as =Values!$P$5:$Q$36.
The column P, from rows 5 to 36, contains all 32 percentages, (correctly)
ranked from smallest at the top to largest at the bottom. The percentage of
329% appears in that list next to the number 7 in column Q, which contains
the numbers from 32 to 1 descending in rows 5 to 36.

However, although the percentage of 329% was correctly placed next to the
number 7 in column Q, the rank of 7th did not appear in O32 as it should.
Instead, I got the #N/A error.

Also, I should mention that the 329% figure is exact. $6119.40 divided by
$1860.00 expressed as a percentage is exactly 329%.

Finally, I should mention that I said in my first message that the problem
is an intermittent one and happened yesterday. Well, infuriatingly, it
didn't happen today, although I will not delete yesterday's version of the
worksheet, so that I have a record of the error.

If I've not given you the information you wanted, please let me know what
else I should post.

Many thanks,

Leslie

 

[Frank Kabel]

Hi
first: Yes this was the kind of information which was very
helpful

One assumption: Though this division seems to return a non
fractional value Excel could treat this differently (see
http://www.cpearson.com/excel/rounding.htm for an
explanantion).


Therefore try the following formula
=VLOOKUP(ROUND(N32,2),range3,2,FALSE)

Frank

 

[Leslie]

Hi, Frank. Thanks for the further information.

I changed the formula in N32, the cell which had returned the #N/A error, to
add the Round to two decimal places function to it. Excel then told me that
that formula was inconsistent with others near it. I told it to ignore that
error. I then ran the macro which should update the rankings of percentages
and it all worked. It showed the 7 in N32 it had been refusing to show
earlier.

Intrigued by that, I then evaluated a number of the other 31 formulae in
column N. Each was using in the relevant formula the percentage expressed as
a number, in some cases down to 15 decimal places or so, but it still worked
alright. I just don't understand, nor, I have to confess, did I feel I was
better informed after reading the Pearson article, which was simply beyond
me.

However, although I'd like to understand, I wouldn't refuse to use something
that works just because I don't understand it. I think I'll now change all
32 formulae to add the ROUND function, just to be safe in the future.

Thank very much for your patience and your help.

Leslie