I have a lookup formula that looks up data in the last column. The last
column contains zeros so the formula returns a non zero number from the
column before. How do I fix to have the lookup bring the numbers in the
column including zero.

Hi,

If I understand, you want to lookup say in column H and bring back the
result in column G. If so you can't use VLOOKUP, instead use

=INDEX(G1:G100,MATCH(A1,H1:H100,0))
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"kenbquik" wrote:

I have a lookup formula that looks up data in the last column. The last
column contains zeros so the formula returns a non zero number from the
column before. How do I fix to have the lookup bring the numbers in the
column including zero.

Hi Shane,

Here's what I've got:
sheet#1 (data input below)
Fresno
A B C D E
1 Fruit Jan'09 Feb '09 Mar '09 Apr '09
2 pear 23 31 12 0
3 cherry 15 3 0 0
4 Apple 12 7 0 0

Sacramento
A B C D E
7 Fruit Jan'09 Feb '09 Mar '09 Apr '09
8 pear 2 1 10 0
9 cherry 10 3 0 0
10 Apple 3 4 0 0

Total California (sum of Fresno and Sacramento)
A B C D E
12 Fruit Jan'09 Feb '09 Mar '09 Apr '09
13 pear 25 32 22 0
14 cherry 25 6 0 0
15 Apple 15 11 0 0

Heres what I get in the table:
sheet #2 (table result)
=LOOKUP(1E+100,1/('sheet1'!B13:E13),'sheet1'!B13:E13)
=LOOKUP(1E+100,1/('sheet1'!B14:E14),'sheet1'!B14:E14)
=LOOKUP(1E+100,1/('sheet1'!B15:E15),'sheet1'!B15:E15)

Total Jan'09 Feb '09 Mar '09
pear 25 32 22
cherry 25 6 6
Apple 15 11 11

Heres what I want in the table
total Jan'09 Feb '09 Mar '09
pear 25 32 22
cherry 25 6 0
Apple 15 11 0

How can I get above?

I know that probably I'll have to do a offset formula to so the table will
know how to pick up the previous months (Jan & Feb) ---- Which I can't
figure out as well!

I'm confused.... I've read so much, I don't know what to do. like a lookup,
index, offset, name range.


Please guide me to the best answer. Any help appreciated!



"Shane Devenshire" wrote:

Hi,

If I understand, you want to lookup say in column H and bring back the
result in column G. If so you can't use VLOOKUP, instead use

=INDEX(G1:G100,MATCH(A1,H1:H100,0))
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"kenbquik" wrote:

I have a lookup formula that looks up data in the last column. The last
column contains zeros so the formula returns a non zero number from the
column before. How do I fix to have the lookup bring the numbers in the
column including zero.