MrExcel Publishing
Your One Stop for Excel Tips & Solutions

lookup table


Posted by mcarter973 on August 09, 2001 11:26 AM

I'm using a table to lookup values on a monthly basis.

column A: January, February, March, etc.
column B: Quantity (cell B13 is the YTD figure).

I want to create a formula that will lookup the month in column A and sum the YTD numbers in column B.

For example:

January 8
February 10
March 23
April 17

YTD 58

If I look up February I want to sum just January and February (8 and 10).

Thanks


Posted by Aladin Akyurek on August 09, 2001 11:38 AM

How did you enter the dates? Just as you typed? If so, what is its format?

Aladin

Posted by Mark W. on August 09, 2001 11:40 AM

=SUM($C$8:OFFSET($B$7,MATCH("February",$B$8:$B$11,0),1))

Posted by mcarter973 on August 09, 2001 11:41 AM

They are month-end dates (01/31/01,etc).

Posted by Mark W. on August 09, 2001 11:44 AM

It doesn't really matter what the dates in your
list are... just use: =SUM($C$8:OFFSET($B$7,2,1)) They are month-end dates (01/31/01,etc). : How did you enter the dates? Just as you typed? If so, what is its format? : Aladin :

Posted by Aladin Akyurek on August 09, 2001 11:46 AM

I'll assume also a date-formatted date in say C1, your lookup value.

In B13 enter: =SUMPRODUCT((MONTH(A1:A12)=MONTH(C1))*(B1:B12))

Aladin

========== They are month-end dates (01/31/01,etc). : How did you enter the dates? Just as you typed? If so, what is its format? : Aladin :

Posted by Aladin Akyurek on August 09, 2001 11:49 AM

Correction

Posted by Mark W. on August 09, 2001 11:51 AM

If your date's in C1...

...as Aladin assumed then...

=SUM($C$8:OFFSET($B$7,MONTH(C1),1))

...will do the trick. There's no need for a
lot of intermediate calculations. Just "jump to
the spot" and sum!!!

Posted by mcarter973 on August 09, 2001 12:06 PM

Re: Correction

Aladin -

It didn't work - is this a CSE formula? =SUMPRODUCT((MONTH(A1:A12)<=MONTH(C1))*(B1:B12))


Posted by Aladin Akyurek on August 09, 2001 12:21 PM

Re: Correction

No, it is not.

The formula assumes a date in C1 (criterion date), dates in column A (which need not to be sorted), and with these dates corresponding numbers in column B.

If the month February is the criterion, =MONTH(C1) must return 2. Also, of course February dates in A must return 2 when MONTH applied to each of them. Is this perhaps the reason why the formula does not work?

Aladin