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
by