Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index left match or lookup??? | New Users to Excel | |||
I want result of a function in the cell and not the function itsel | Excel Worksheet Functions | |||
lookup value and return result in column to left | Excel Worksheet Functions | |||
how to get left or right cell info after getting the result for ma | Excel Worksheet Functions | |||
Formatting result of Index function | Excel Worksheet Functions |