Cell to the left of an Index function result
 

Hi,

I have the following data:

Jan YTD Feb YTD Mar YTD
Acct#1 5 10 15
Acct#2 3 6 9
Acct#3 4 8 12


On a separate March analysis tab, using an
=Index(Range,Match(...),Match(...)) formula, I have pulled in the balance of
Acct #2 for Mar YTD. Now I would like to calculate the Mar MTD amount by
subtracting the Feb YTD balance. Do I have to do a second =Index formula to
pull the Feb YTD balance, or is there a function or formula that would simply
pick up and subtract the amount in the cell to the left my Acct#2, Mar YTD
Index result?

Thanks

Cell to the left of an Index function result
 

=INDEX(Range,MATCH(acct,Accts,0),MATCH(month,Month s,0))-INDEX
(Range,MATCH(acct,Accts,0),MATCH(month,Months,0)-1)

This allows you to refer to the same month in both MATCH that you use.
It is of course problematic for January, but you don't want a
difference for Jan.

You can also use OFFSET as in the following expression for Feb:

OFFSET(INDEX(Range,MATCH(acct,Accts,0),MATCH(month ,Months,0)),0,-1)

HTH
Kostis Vezerides

On Nov 24, 4:53*pm, JustBreathe
wrote:
Hi,

I have the following data:

* * * * * * Jan YTD * *Feb YTD * * Mar YTD
Acct#1 * * * 5 * * * * * * *10 * * * * * * 15
Acct#2 * * * 3 * * * * * * * 6 * * * * * * * 9
Acct#3 * * * 4 * * * * * * * 8 * * * * * * *12

On a separate March analysis tab, using an
=Index(Range,Match(...),Match(...)) *formula, I have pulled in the balance of
Acct #2 for Mar YTD. *Now I would like to calculate the Mar MTD amount by
subtracting the Feb YTD balance. *Do I have to do a second =Index formula to
pull the Feb YTD balance, or is there a function or formula that would simply
pick up and subtract the amount in the cell to the left my Acct#2, Mar YTD *
Index result?

Thanks

Cell to the left of an Index function result
 

Hi,

Here is one way:

=SUMPRODUCT(OFFSET(A1,MATCH(A10,A2:A4,0),MATCH(A9, B1:D1,0),,2)*{-1,1})

A1 is the top left corner above and to the left of Range in you example

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"JustBreathe" wrote:

Hi,

I have the following data:

Jan YTD Feb YTD Mar YTD
Acct#1 5 10 15
Acct#2 3 6 9
Acct#3 4 8 12


On a separate March analysis tab, using an
=Index(Range,Match(...),Match(...)) formula, I have pulled in the balance of
Acct #2 for Mar YTD. Now I would like to calculate the Mar MTD amount by
subtracting the Feb YTD balance. Do I have to do a second =Index formula to
pull the Feb YTD balance, or is there a function or formula that would simply
pick up and subtract the amount in the cell to the left my Acct#2, Mar YTD
Index result?

Thanks