LOOKUP Function
 

D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for
the year 2010 is 0.0 as we have not entered into that year as of yet. The
same for the rest of the row up to AJ14. I am looking for a formula that will
enter in cell B35 the total accumulated hours of just the current year from
D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter
0.0 from cell AJ14. How can I get it to read the current yearly hours from
Dec 2009 in cell G14?

LOOKUP Function
 

Hi,

Possibly this

=LOOKUP(2,1/(D14:AJ140),D14:AJ14)

Mike

"Loadmaster" wrote:

D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for
the year 2010 is 0.0 as we have not entered into that year as of yet. The
same for the rest of the row up to AJ14. I am looking for a formula that will
enter in cell B35 the total accumulated hours of just the current year from
D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter
0.0 from cell AJ14. How can I get it to read the current yearly hours from
Dec 2009 in cell G14?

LOOKUP Function
 

Hi,

It looks to me like you should use

=INDEX(D14:AJ14,,MATCH(YEAR(NOW()),D1:AJ1,))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Loadmaster" wrote:

D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for
the year 2010 is 0.0 as we have not entered into that year as of yet. The
same for the rest of the row up to AJ14. I am looking for a formula that will
enter in cell B35 the total accumulated hours of just the current year from
D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter
0.0 from cell AJ14. How can I get it to read the current yearly hours from
Dec 2009 in cell G14?

LOOKUP Function
 

Thank-you it does work.

"Mike H" wrote:

Hi,

Possibly this

=LOOKUP(2,1/(D14:AJ140),D14:AJ14)

Mike

"Loadmaster" wrote:

D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for
the year 2010 is 0.0 as we have not entered into that year as of yet. The
same for the rest of the row up to AJ14. I am looking for a formula that will
enter in cell B35 the total accumulated hours of just the current year from
D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter
0.0 from cell AJ14. How can I get it to read the current yearly hours from
Dec 2009 in cell G14?

LOOKUP Function
 

Hi,

The LOOKUP version would be

=LOOKUP(YEAR(NOW()),D1:AJ1,D14:AJ14)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Loadmaster" wrote:

D1:AJ1 have the years 2006:2038. C2:C13 have the months JAN:DEC. D14:AJ14
total the columns D2:D13, E2:E13 etc.... The total in AH14 which is 0.0 for
the year 2010 is 0.0 as we have not entered into that year as of yet. The
same for the rest of the row up to AJ14. I am looking for a formula that will
enter in cell B35 the total accumulated hours of just the current year from
D14:AJ14. I tried =LOOKUP(99^99,D14:AJ14) but naturally it is going to enter
0.0 from cell AJ14. How can I get it to read the current yearly hours from
Dec 2009 in cell G14?