Lookup looks to the prior column if zero appears in the lookup col
 

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.

Lookup looks to the prior column if zero appears in the lookup col
 

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.

Lookup looks to the prior column if zero appears in the lookup
 

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.