## N/A with vlookup

### N/A with vlookup

I'm attempting to reteieve data from a table (on a seperate worksheet)
using vlookup. For all but one cell I get an N/A error. Even though
the formulas for the other cells are exactly like the formula for the
one cell that works.
I've tried everything, read all the help files, all the data types for
the cells match. I can see nothing wrong. This is the first time I've
used this particualar function, but I'm pretty sure it's right. The
fact that it works for one cell should indicate that I'm at least
partially correct. Here the formula I'm using:
=VLOOKUP(J9,Tables!A5:Y27,E9-27)
Tables containg an individual's weight based on hight(J9 value) and
age (E9-27) which converts to a colunmn number 2:25.
I would greatly appriciate any assistance you can offer.

Thank you
Michelle

### N/A with vlookup

Help for VLOOKUP is quite clear.  Since you do not specify the optional
range_lookup variable, VLOOKUP expects that Tables!A5:A27 is sorted in
ascending order.  #N/A indicates that VLOOKUP believes there to be no
matches at all, which in the context of assumed ascending order, means
that Tables!A5 > J9.

If there should be a match somewhere, either sort Tables!A5:Y27 in
ascending column A order, or use
=VLOOKUP(J9,Tables!A5:Y27,E9-27,FALSE)
to require exact matches without sorting.

If you still have the problem, try the following:

Verify that either COUNT(J9) = 0 = COUNT(Tables!A5:A27) or that
COUNT(J9) = COUNT(Tables!A5:A27)/23.  Otherwise some of your heights are
numbers while others are text.  It is not sufficient to change the
format of the cell to change the data type, you must reenter the value
after you change the format.  Alternately, if you want them all to be
numbers, copy a zero value, and Past Special|Add over both ranges to
ensure that everything is a number.

Jerry

> I'm attempting to reteieve data from a table (on a seperate worksheet)
> using vlookup. For all but one cell I get an N/A error. Even though
> the formulas for the other cells are exactly like the formula for the
> one cell that works.
> I've tried everything, read all the help files, all the data types for
> the cells match. I can see nothing wrong. This is the first time I've
> used this particualar function, but I'm pretty sure it's right. The
> fact that it works for one cell should indicate that I'm at least
> partially correct. Here the formula I'm using:
>            =VLOOKUP(J9,Tables!A5:Y27,E9-27)
>   Tables containg an individual's weight based on hight(J9 value) and
> age (E9-27) which converts to a colunmn number 2:25.
> I would greatly appriciate any assistance you can offer.

>                            Thank you
>                               Michelle

### N/A with vlookup

Long shot based on a minor point - I used to get N/As with vlookups
frequently because I used to forget to anchor the table range.... you don't
have anchors in the formula you quoted below. You are using
"Tables!\$A\$5:\$Y\$27" if you're copying the formula, aren't you?

Lynne Cartwright

Quote:> I'm attempting to reteieve data from a table (on a seperate worksheet)
> using vlookup. For all but one cell I get an N/A error. Even though
> the formulas for the other cells are exactly like the formula for the
> one cell that works.
> I've tried everything, read all the help files, all the data types for
> the cells match. I can see nothing wrong. This is the first time I've
> used this particualar function, but I'm pretty sure it's right. The
> fact that it works for one cell should indicate that I'm at least
> partially correct. Here the formula I'm using:
>            =VLOOKUP(J9,Tables!A5:Y27,E9-27)
>   Tables containg an individual's weight based on hight(J9 value) and
> age (E9-27) which converts to a colunmn number 2:25.
> I would greatly appriciate any assistance you can offer.

>                            Thank you
>                               Michelle

There seems to be a longstanding (I found comments in this group going
back as far as 1993!) bug in MS Excel when trying to use the SUM or
SUMPRODUCT functions using a VLOOKUP or HLOOKUP array as an argument.
As an example, the formula:
{=SUM(VLOOKUP(ArrayName,b5:b10,4))}
will not return the correct result if it is entered as an array
formula into a single cell. To get the correct value, the formula must
be entered as an array formula across at least 2 cells, in which case
the correct result appears each of those cells. While not
life-threatening, this fix is ugly and does cause some concern as to
its underlying cause. At the very least, it is *extremely* confusing
to people looking at such spreadsheets who are unaware of the bug's
existence.

Can an MVP or someone else affiliated w/ MSFT explain a) if there is a
reason for this behavior (maybe it's a "feature"?), b) if there is a
better workaround, and c) why it hasn't been fixed in 10 years?!?

Thank you.

-geoff