If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Vlookup and "-" negative numbers are giving me a #N/A
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Vlookup and "-" negative numbers are giving me a #N/A

#1
January 15th 06, 12:21 AM posted to microsoft.public.excel.misc
 DMB external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

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
#2
January 15th 06, 12:41 AM posted to microsoft.public.excel.misc
 Troubled User external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

That probably means that it can't find it in the list. If the last portion
of the Lookup function is selected as "true" 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

WB

"DMB" wrote:

> 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

#3
January 15th 06, 12:48 AM posted to microsoft.public.excel.misc
 pinmaster external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

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

"DMB" wrote:

> 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

#4
January 15th 06, 12:56 AM posted to microsoft.public.excel.misc
 DMB external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

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 "xxx"
#5
January 15th 06, 01:19 AM posted to microsoft.public.excel.misc
 Dave Peterson external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

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:
http://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:
>
> 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 "xxx"

--

Dave Peterson
#6
January 15th 06, 01:41 AM posted to microsoft.public.excel.misc
 DMB external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

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.

#7
January 15th 06, 01:58 AM posted to microsoft.public.excel.misc
 DMB external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

Actually the negative problem still exists
#8
January 15th 06, 02:06 AM posted to microsoft.public.excel.misc
 Peo Sjoblom external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

If I use

=VLOOKUP(B5,Capacity,2)

with 3233 in B5

I get phd4, not good?

--
Regards,

Peo Sjoblom

Portland, Oregon

"DMB" > wrote in message
...
> 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 "xxx"

#9
January 15th 06, 03:05 PM posted to microsoft.public.excel.misc
 Dave Peterson external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

Make sure that the first column in Capacity is a real number
select an empty cell
edit|copy
select that first column

Then try it again.

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

DMB wrote:
>
> 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.

--

Dave Peterson
#10
January 15th 06, 04:42 PM posted to microsoft.public.excel.misc
 DMB external usenet poster Posts: n/a
Vlookup and "-" negative numbers are giving me a #N/A

No it shoud have been a phd5.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

All times are GMT +1. The time now is 05:59 PM.