Please help - subtotal using vlookup depending on variable month

[robstton]

Hi All,
I'm trying to find a value from table A in table B using vlookup and then
sum the months YTD depending on the month. So for example if I enter April
or 4
table A
Month__4___<---value entered manually
name $ytd
bob 142
mob 117
gob 152

table b
PC Jan Feb Mar Apr May Jun Jul..........Dec
bob 22 50 45 25 56 78 89 12
mob 32 25 25 35 52 85 24 15
gob 32 20 15 85 95 65 45 85

Many thanks

 

[Dave Peterson]

Maybe something like:

=SUMIF(Sheet2!A:A,"bob",
INDEX(Sheet2!A:M,,MATCH(TEXT(DATE(2008,$A$1,1),"mmm"),Sheet2!$A$1:$M$1,0)))

You can replace "bob" with the cell that contains that text (like $a2):

=SUMIF(Sheet2!A:A,$A2,
INDEX(Sheet2!$A:$M,,MATCH(TEXT(DATE(2008,$A$1,1),"mmm"),Sheet2!$A$1:$M$1,0)))

This assumes that the table b is on Sheet2 in columns A:M and the month number
is in A1 of the sheet with the formula.

 

[smartin]

Hi All,
I'm trying to find a value from table A in table B using vlookup and then
sum the months YTD depending on the month. So for example if I enter April
or 4
table A
Month__4___<---value entered manually
name $ytd
bob 142
mob 117
gob 152

table b
PC Jan Feb Mar Apr May Jun Jul..........Dec
bob 22 50 45 25 56 78 89 12
mob 32 25 25 35 52 85 24 15
gob 32 20 15 85 95 65 45 85

Many thanks

One way... This will work as long as you are using month numbers in
TABLEA (not names or abbreviations). How you label the months in TABLEB
doesn't matter so much as long as they start with January and are
chronological and continuous.

First I set up TABLEB somewhere as a named range -- this helps make the
solution more universal.

Then I set up TABLEA in A1:B5, where B1 has the month number to look up
and the names are in A3:A5.

=SUM(OFFSET(TABLEB,MATCH(A3,OFFSET(TABLEB,0,0,999,1),0)-1,1,1,$B$1))

The "999" in this formula just need to be large enough to accommodate
the number of rows in TABLEB.