index, sumproduct, or other

[JustBreathe]

I'm trying to write a formula that will pull the YTD total for the month of
interest. The problem is each month has two columns, MTD and YTD. When I
use an index formula it pulls the MTD total which is the first of the two
columns. I am having little luck with writing a sumproduct formula that will
work either. Any suggestions here? (Changing the headers naming convention
is really not an option because it will just revert back each time the data
extract which creates this report is refreshed.)

A B C D E
Jan-09 Jan-09 Feb-09 Feb-09
MTD YTD MTD YTD
111 $5 $5 $3 $8
222 $1 $1 $4 $5
Total $6 $6 $7 $13

 

[T. Valko]

Do you want the result to come from the "Total" row or one of the "item"
rows?

 

[Ashish Mathur]

Hi,

You could try this. This will return the result from the total row

=INDEX($B$1:$E$5,MATCH("Total",A1:A5,0),MATCH(A8,B1:E1,0)+1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com