Home

INDEX MATCH Formula Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-1094638%22%20slang%3D%22en-US%22%3EINDEX%20MATCH%20Formula%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1094638%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20sure%20there%20is%20a%20million%20of%20these%20on%20here%20but%20I%20have%20been%20spinning%20my%20wheels%20on%20this%20for%20a%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20issues%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20do%20a%20year%20to%20date%20formula%20on%20a%20deck%20tab%20linked%20to%20a%20large%20P%26amp%3BL%20tab.%20If%20im%20looking%20up%20March%20instead%20of%20pulling%20year%20to%20date%20(Jan%2C%20Feb%2C%20Mar)%20it%20is%20pulling%20Mar%2C%20April%2C%20May.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20issue%20is%20im%20pulling%20a%20balance%20sheet%20with%20a%20month%20to%20date%20and%20it%20is%20pulling%20other%20balance%20sheet%20cells.%20Here%20is%20the%20file%20and%20the%20two%20formulas.%20I%20am%20trying%20to%20make%20this%20automated%20so%20I%20can%20change%20a%20date%20in%20one%20tab%20and%20the%20whole%20model%20works%2C%20these%20are%20the%20last%20issues%20im%20having.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYTD%20Formula%3C%2FP%3E%3CP%3E%3DSUMIF(IS!%24P%247%3A%24AA%247%2C%22YTD%22%2C(INDEX(IS!%24P%2411%3A%24AA%2477%2CMATCH('YTD%20Summary'!A6%2CIS!%24A%2411%3A%24A%2477%2C0)*MATCH('YTD%20Summary'!%24C%245%2CIS!%24P%248%3A%24AA%248%2C0)%2CMATCH('YTD%20Summary'!%24B%241%2CIS!%24P%249%3A%24AA%249))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBalance%20Sheet%3C%2FP%3E%3CP%3E%3DINDEX(BS!%24A%248%3A%24AX%2470%2CMATCH('Balance%20Sheet'!A5%2CBS!%24A%248%3A%24A%2470)%2CMATCH('Balance%20Sheet'!%24C%241%2CBS!%24A%247%3A%24AX%247))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1094638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Regular Visitor

Im sure there is a million of these on here but I have been spinning my wheels on this for a day.

 

Two issues

 

I'm trying to do a year to date formula on a deck tab linked to a large P&L tab. If im looking up March instead of pulling year to date (Jan, Feb, Mar) it is pulling Mar, April, May.

 

The other issue is im pulling a balance sheet with a month to date and it is pulling other balance sheet cells. Here is the file and the two formulas. I am trying to make this automated so I can change a date in one tab and the whole model works, these are the last issues im having.

 

YTD Formula

=SUMIF(IS!$P$7:$AA$7,"YTD",(INDEX(IS!$P$11:$AA$77,MATCH('YTD Summary'!A6,IS!$A$11:$A$77,0)*MATCH('YTD Summary'!$C$5,IS!$P$8:$AA$8,0),MATCH('YTD Summary'!$B$1,IS!$P$9:$AA$9))))

 

Balance Sheet

=INDEX(BS!$A$8:$AX$70,MATCH('Balance Sheet'!A5,BS!$A$8:$A$70),MATCH('Balance Sheet'!$C$1,BS!$A$7:$AX$7))