VLOOKUP Error Values

[MDW]

Say I've got the function

VLOOKUP(B:B,Cards!$A:$M,9,0)

on a sheet.

If the value in column B doesn't appear in the range "Cards!$A:$M", this
formula returns an error, as it should. However, it seems to me that
sometimes the error is "#N/A!" and sometimes it's "#REF!".

Is there any significance to which of these errors appears? To the best of
my abilities, it seems as though they signify the same thing - the absense of
the lookup value in the table array.

 

[Peo Sjoblom]

Far from it, the #N/A error comes from the lookup value not being found
while the #REF! error can be for instance if you
delete a cell that is being referenced by a formula

Regards,

Peo Sjoblom

 

[gatwickxx]

You should be stating a given cell in column B to search for. The las
argument of your function should be FALSE so that it will only searc
for the eact value in column B.

For example:
=VLOOKUP(B1,Cards!$A:$M,9,FALSE)
should be used copied down. This will look for the value in B1 i
column 9 (column I of the cards sheet). This way the only erro
message you should get is #NA is the B1 value is not found anywhere i
your range

 

[MDW]

Ugh....I apologize.... The other error I get is "#VALUE!". not "#REF!".

Same question, though.

 

[Peo Sjoblom]

If you calculate a cell with text you get a #VALUE! error, i.e.

=A2*10

if A2 holds either a text like "abcd" or a null vlaue like ""
then you get that error

Or if you use more than one cell as lookup

=VLOOKUP(A1:A3,B2:C7,2,0)

there are other reasons as well with regards to array formulas but they
#N/A and #VALUE! are not similar


Regards,

Peo Sjoblom