software

How do I deal with the negative numbers? If possible I would rather not
complicate my formula with an If statement.

Thanks again and again and again and again and again

That probably means that it can't find it in the list. If the last portion
of the Lookup function is selected as quot;truequot; the list needs to be in
ascending order. If the number you are looking up is not greater than the
first number in the list, it will return #N/A. Give me a little more info on
the problem and I will try to help you through it!

WB

quot;DMBquot; wrote:

gt; How do I deal with the negative numbers? If possible I would rather not
gt; complicate my formula with an If statement.
gt;
gt; Thanks again and again and again and again and again

What's the last parameter of the VLOOKUP? If you're using TRUE or 1, then
your table might have to be sorted in ascending order, negetives to
positives. Or maybe your looking up values with decimal places that are not
showing.

HTH
JG

quot;DMBquot; wrote:

gt; How do I deal with the negative numbers? If possible I would rather not
gt; complicate my formula with an If statement.
gt;
gt; Thanks again and again and again and again and again

My table is

CAPACITY
0 xxx
1000 phd2
2000 phd3
3000 phd4
4000 phd5
5000 phd6

B5 = 3233
=vlookup(B5, CAPACITY, 1)

And now I get nothing but quot;xxxquot;

If the first column of that table is numeric (real numbers) and if b5 is numeric
(real numbers), then I get 3000 back from that formula.

If b5 contains the text '3233, then I get #n/a

Debra Dalgleish has some trouble shooting tips at:
www.contextures.com/xlFunctions02.html#Trouble

ps. Did you want to return column 2:
=vlookup(B5, CAPACITY, 2)
(assuming that Capacity is a 2 column table.)

DMB wrote:
gt;
gt; My table is
gt;
gt; CAPACITY
gt; 0 xxx
gt; 1000 phd2
gt; 2000 phd3
gt; 3000 phd4
gt; 4000 phd5
gt; 5000 phd6
gt;
gt; B5 = 3233
gt; =vlookup(B5, CAPACITY, 1)
gt;
gt; And now I get nothing but quot;xxxquot;

--

Dave Peterson

I figured out that the column numbers are relitive to the table and not the
sheet. I also found that the result is the row before the next higher value.
ROW1 ROW2 ROW3
CAPACITY HOLDOWN
0 n.r.
1000 PHD1
2000 PHD2
3000 PHD3
4000 PHD4

=VLOOKUP(1500, CAPACITY, 3) RETURNS A BLANK
=VLOOKUP(1500, CAPACITY, 2) RETURNS n.r.

tHE NEGATIVE PROBLEM SEEMS TO HAVE GONE AWAY. Is the return value working
correctly? I am going to experiment with the true/false value. The problem
with this is anyone entering data will be confused by the table.

Actually the negative problem still exists

If I use

=VLOOKUP(B5,Capacity,2)

with 3233 in B5

I get phd4, not good?

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;DMBquot; gt; wrote in message
...
gt; My table is
gt;
gt; CAPACITY
gt; 0 xxx
gt; 1000 phd2
gt; 2000 phd3
gt; 3000 phd4
gt; 4000 phd5
gt; 5000 phd6
gt;
gt; B5 = 3233
gt; =vlookup(B5, CAPACITY, 1)
gt;
gt; And now I get nothing but quot;xxxquot;Make sure that the first column in Capacity is a real number
select an empty cell
edit|copy
select that first column
edit|Paste special|check add.

Then try it again.

If that doesn't work, what is Capacity defined as--just share the address of
that table.

DMB wrote:
gt;
gt; I figured out that the column numbers are relitive to the table and not the
gt; sheet. I also found that the result is the row before the next higher value.
gt; ROW1 ROW2 ROW3
gt; CAPACITY HOLDOWN
gt; 0 n.r.
gt; 1000 PHD1
gt; 2000 PHD2
gt; 3000 PHD3
gt; 4000 PHD4
gt;
gt; =VLOOKUP(1500, CAPACITY, 3) RETURNS A BLANK
gt; =VLOOKUP(1500, CAPACITY, 2) RETURNS n.r.
gt;
gt; tHE NEGATIVE PROBLEM SEEMS TO HAVE GONE AWAY. Is the return value working
gt; correctly? I am going to experiment with the true/false value. The problem
gt; with this is anyone entering data will be confused by the table.

--

Dave Peterson

No it shoud have been a phd5.