lookup question

[Harvey Waxman]

Can a zero be a value in the first column of a lookup?

When I try to use a zero I get a #NA error.

 

[JE McGimpsey]

Can a zero be a value in the first column of a lookup?

When I try to use a zero I get a #NA error.

The short answer is "yes".

The long answer would take knowing what your formula is, including which
of the three lookup functions you're talking about.

 

[Harvey Waxman]

The short answer is "yes".

The long answer would take knowing what your formula is, including which
of the three lookup functions you're talking about.

This is the formula: =LOOKUP(A1,lookups!$A$19:$A$54,"CL"&lookups!$B$19:$B$54)

The range a19:a54 on the sheet named lookups contains sorted but non
consecutive numeric values from 0 to 92 and the range b19:b54 contains either
the number 1 or 2.

If a1 is a 0 it displays #NA. I worked around it with an IF statement but I'd
still like to understand what I am doing wrong.

Thanks

 

[JE McGimpsey]

This is the formula:
=LOOKUP(A1,lookups!$A$19:$A$54,"CL"&lookups!$B$19:$B$54)

The range a19:a54 on the sheet named lookups contains sorted but non
consecutive numeric values from 0 to 92 and the range b19:b54 contains either
the number 1 or 2.

If a1 is a 0 it displays #NA. I worked around it with an IF statement but
I'd
still like to understand what I am doing wrong.

Your formula returns a value for me as long as the first number in
lookup!A19:A54 is less than or equal to zero. Otherwise it *should*
return #N/A. see Help:

 

[Harvey Waxman]

Your formula returns a value for me as long as the first number in
lookup!A19:A54 is less than or equal to zero. Otherwise it *should*
return #N/A. see Help:

The vectors in the 'lookups' sheet
A B
18 0 1
19 1 1
20 2 2
21 3 2
22 4 2
23 5 2
24 6 2
25 7 2
26 8 1
27 9 2
28 10 2
etc.


The results:
A B
1 1 CL1 formula =LOOKUP(A1,lookups!$A$18:$A$54,"CL"&lookups!$B$18:$B$54)
2 1 CL1
3 0 #N/A
4 0 #N/A
5 1 CL1
6 1 CL1
7 0 #N/A
8 1 CL1
9 1 CL1
10 1 CL1
11 0 CL1
12 0 CL1
13 0 CL1

Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com
Remove thefrown to email me

 

[JE McGimpsey]

The vectors in the 'lookups' sheet
A B
18 0 1
19 1 1
20 2 2
21 3 2
22 4 2
23 5 2
24 6 2
25 7 2
26 8 1
27 9 2
28 10 2
etc.


The results:
A B
1 1 CL1 formula
=LOOKUP(A1,lookups!$A$18:$A$54,"CL"&lookups!$B$18:$B$54)
2 1 CL1
3 0 #N/A
4 0 #N/A
5 1 CL1
6 1 CL1
7 0 #N/A
8 1 CL1
9 1 CL1
10 1 CL1
11 0 CL1
12 0 CL1
13 0 CL1

Check your range references. There's no way that both line 3 and line 11
should produce different results, assuming you copied the formula in B1
down. Note that your original post had the lookup vectors $A$19:$A$54,
in which case #N/A would be the *expected* result, since the first value
in the vector would be >0.

Also - are the values in column A of your results sheet calculated? If
so, is it possible that, while displaying 0, the calculation is
returning a small negative value?

 

[Harvey Waxman]

Check your range references. There's no way that both line 3 and line 11
should produce different results, assuming you copied the formula in B1
down. Note that your original post had the lookup vectors $A$19:$A$54,
in which case #N/A would be the *expected* result, since the first value
in the vector would be >0.

Also - are the values in column A of your results sheet calculated? If
so, is it possible that, while displaying 0, the calculation is
returning a small negative value?

I created another set of vectors and it works as expected. I have no idea why
the first ones refused to cooperate. I tried changing the format to no avail.
I suspect that they needed to be 'coerced' but I forgot how. You told me once.

copy "1" and paste special to the range using multiply? I bet that was the
trouble.

thanks