# Index/Match Formula?



## Katich (Dec 21, 2022)

Hello,
I have a file that i'm trying to find a way to pull multiple years data off of. I'm using the Index/Match formula to pull my current year data but i have a prior year column i'd like to pull the prior year but not sure how to.  I essentially have 2 tabs, one that has my report, the other that has the data.  My formula is pulling from the data tab.   Column D is the column i need my 2021 data.  It's showing 2022 since i haven't figure out what to do yet. 

DoubleTree Analysis.xlsxABCDEF1January-222 ACT  BUD  PRIOR  BUD VAR  PY VAR 3 Rooms Available 4,920#N/A4,920#N/A04 Rooms Sold 2,403#N/A2,403#N/A05 Occupancy 48.84%#N/A48.84%#N/A0.00%6 ADR 63.64#N/A63.64#N/A-7 Rooms RevPAR 31.08#N/A31.08#N/A-8 Operating Revenue 9 Rooms Revenue 152,935#N/A152,935#N/A010 Food & Beverage Revenue 67,351#N/A67,351#N/A011 Other Minor Operated Depts. Rev. 4,330#N/A4,330#N/A012 Total Operating Revenue 224,616#N/A224,616#N/A013 Departmental Expenses 14 Rooms Expenses 74,131#N/A74,131#N/A015 Food & Beverage Expenses 96,284#N/A96,284#N/A016 Other Minor Oper. Depts. Expenses 1,852#N/A1,852#N/A017 Total Departmental Expenses 172,267#N/A172,267#N/A018 Total Departmental Profit 52,349#N/A52,349#N/A019 Undistributed Operating Expenses 20 Administrative & General 64,709#N/A64,709#N/A021 Information & Telecomm. Systems 9,304#N/A9,304#N/A022 Sales & Marketing 45,557#N/A45,557#N/A023 Property Operations & Maint. 25,859#N/A25,859#N/A024 Utilities/Franchise Royalty Fees 9,969#N/A9,969#N/A025 Total Undistributed Expenses 155,398#N/A155,398#N/A026 Gross Operating Profit (103,049)#N/A(103,049)#N/A027 Management Fees 5,615#N/A5,615#N/A028 Propert & other Taxes/Insurance 16,859#N/A16,859#N/A029 FF&E Reserve 0#N/A0#N/A030 EBITDA (125,523)#N/A(125,523)#N/A0DOUBLETREE HOTEL- TrackerCell FormulasRangeFormulaB3,D3B3=INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))C3:C7C3=INDEX('Data - Actuals'!P3:AA3,1,MATCH($A$1,'Data - Actuals'!$B$2:$M$2,0))E3:E7,E20:E30,E14:E18,E9:E12E3=B3-C3F3:F7,F20:F30,F14:F18,F9:F12F3=B3-D3B4,D4B4=INDEX('Data - Actuals'!$B$3:$BZ$3,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B5,D5B5=INDEX('Data - Actuals'!$B$4:$BZ$4,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B6,D6B6=INDEX('Data - Actuals'!$B$5:$BZ$5,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B7,D7B7=INDEX('Data - Actuals'!$B$6:$BZ$6,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B9,D9B9=INDEX('Data - Actuals'!$B$9:$BZ$9,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))C9:C11,C20:C24,C27:C29,C14:C16C9=INDEX('Data - Actuals'!P10:AA10,1,MATCH($A$1,'Data - Actuals'!$B$2:$M$2,0))B10,D10B10=INDEX('Data - Actuals'!$B$10:$BZ$10,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B11,D11B11=INDEX('Data - Actuals'!$B$11:$BZ$11,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B12:D12,B17:D17B12=SUM(B9:B11)B14,D14B14=INDEX('Data - Actuals'!$B$14:$BZ$14,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B15,D15B15=INDEX('Data - Actuals'!$B$15:$BZ$15,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B16,D16B16=INDEX('Data - Actuals'!$B$16:$BZ$16,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B18:D18B18=B12-B17B20,D20B20=INDEX('Data - Actuals'!$B$20:$BZ$20,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B21,D21B21=INDEX('Data - Actuals'!$B$21:$BZ$21,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B22,D22B22=INDEX('Data - Actuals'!$B$22:$BZ$22,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B23,D23B23=INDEX('Data - Actuals'!$B$23:$BZ$23,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B24,D24B24=INDEX('Data - Actuals'!$B$24:$BZ$24,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B25:D25B25=SUM(B20:B24)B26:D26B26=B12-B17-B25B27,D27B27=INDEX('Data - Actuals'!$B$27:$BZ$27,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B28,D28B28=INDEX('Data - Actuals'!$B$28:$BZ$28,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B29,D29B29=INDEX('Data - Actuals'!$B$29:$BZ$29,1,MATCH($A$1,'Data - Actuals'!$B$1:$BZ$1,0))B30:D30B30=B26-B27-B28-B29Named RangesNameRefers ToCells'DOUBLETREE HOTEL- Tracker'!Print_Area='DOUBLETREE HOTEL- Tracker'!$A$1:$M$34B3:D7, B9:D11, B14:D16, B20:D24, B27:D29Cells with Data ValidationCellAllowCriteriaA1:F1List='Data - Actuals'!$B$1:$AN$1


----------



## Fluff (Dec 21, 2022)

Maybe
	
	
	
	
	
	



```
=INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH(EDATE($A$1,-12),'Data - Actuals'!$B$1:$BZ$1,0))
```


----------



## Katich (Dec 21, 2022)

Fluff said:


> Maybe
> 
> 
> 
> ...


That worked!  Thank you!  Now my next question is, I have on that same tab a section that shows YTD-2022 (Could be 2021 or any other date).  I know it's not an actual date so that throws a wrench into things.  Here is what i have on the same sheet but a couple columns over.  So the column that has Prior shows value's error since it's not an actual date.  Any suggestions? 

DoubleTree Analysis.xlsxHIJKLM1 YTD-2022 2 ACT  BUD  PRIOR  BUD VAR  PY VAR 3 Rooms Available 49,85659,860#VALUE!-10,004#VALUE!4 Rooms Sold 23,80332,263#VALUE!(8,460)#VALUE!5 Occupancy 47.74%53.90%#VALUE!-6.15%#VALUE!6 ADR 135.92142.90#VALUE!(6.98)#VALUE!7 Rooms RevPAR 64.9077.02#VALUE!(12.12)#VALUE!8 Operating Revenue 9 Rooms Revenue 3,235,4084,610,413#VALUE!(1,375,005)#VALUE!10 Food & Beverage Revenue 1,885,1702,675,068#VALUE!(789,898)#VALUE!11 Other Minor Operated Depts. Rev. 85,928242,935#VALUE!(157,007)#VALUE!12 Total Operating Revenue 5,206,5067,528,416#VALUE!(2,321,910)#VALUE!13 Departmental Expenses 14 Rooms Expenses 985,3641,758,013#VALUE!(772,649)#VALUE!15 Food & Beverage Expenses 1,462,6571,963,121#VALUE!(500,464)#VALUE!16 Other Minor Oper. Depts. Expenses 27,62486,680#VALUE!(59,056)#VALUE!17 Total Departmental Expenses 2,475,6453,807,814#VALUE!(1,332,169)#VALUE!18 Total Departmental Profit 2,730,8613,720,602#VALUE!(989,741)#VALUE!19 Undistributed Operating Expenses 20 Administrative & General 556,031590,534#VALUE!(34,503)#VALUE!21 Information & Telecomm. Systems 88,49974,318#VALUE!14,181#VALUE!22 Sales & Marketing 683,803767,605#VALUE!(83,802)#VALUE!23 Property Operations & Maint. 332,180345,873#VALUE!(13,693)#VALUE!24 Utilities/Franchise Royalty Fees 190,857170,524#VALUE!20,333#VALUE!25 Total Undistributed Expenses 1,851,3701,948,854#VALUE!(97,484)#VALUE!26 Gross Operating Profit 879,4911,771,748#VALUE!(892,257)#VALUE!27 Management Fees 130,162148,493#VALUE!-18,331#VALUE!28 Propert & other Taxes/Insurance 342,658129,389#VALUE!213,269#VALUE!29 FF&E Reserve 00#VALUE!0#VALUE!30 Income before Non-Oper. Expenses 406,6711,493,866#VALUE!(1,087,195)#VALUE!DOUBLETREE HOTEL- TrackerCell FormulasRangeFormulaI3I3=INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))J3:J7J3=INDEX('Data - Budget'!$B2:$BZ2,1,MATCH($H$1,'Data - Budget'!$B$1:$BZ$1,0))K3:K7K3=INDEX('Data - Actuals'!$B2:$BZ2,1,MATCH(EDATE($H$1,-12),'Data - Actuals'!$B$1:$BZ$1,0))L3:L7,L20:L30,L14:L18,L9:L12L3=I3-J3M3:M7,M20:M30,M14:M18,M9:M12M3=I3-K3I4I4=INDEX('Data - Actuals'!$B$3:$BZ$3,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I5I5=INDEX('Data - Actuals'!$B$4:$BZ$4,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I6I6=INDEX('Data - Actuals'!$B$5:$BZ$5,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I7I7=INDEX('Data - Actuals'!$B$6:$BZ$6,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I9I9=INDEX('Data - Actuals'!$B$9:$BZ$9,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))J9:J11,J20:J24,J27:J29,J14:J16J9=INDEX('Data - Budget'!$B9:$BZ9,1,MATCH($H$1,'Data - Budget'!$B$1:$BZ$1,0))K9:K11,K20:K24,K27:K29,K14:K16K9=INDEX('Data - Actuals'!$B9:$BZ9,1,MATCH(EDATE($H$1,-12),'Data - Actuals'!$B$1:$BZ$1,0))I10I10=INDEX('Data - Actuals'!$B$10:$BZ$10,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I11I11=INDEX('Data - Actuals'!$B$11:$BZ$11,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I12:K12,I17:K17I12=SUM(I9:I11)I14I14=INDEX('Data - Actuals'!$B$14:$BZ$14,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I15I15=INDEX('Data - Actuals'!$B$15:$BZ$15,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I16I16=INDEX('Data - Actuals'!$B$16:$BZ$16,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I18:K18I18=I12-I17I20I20=INDEX('Data - Actuals'!$B$20:$BZ$20,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I21I21=INDEX('Data - Actuals'!$B$21:$BZ$21,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I22I22=INDEX('Data - Actuals'!$B$22:$BZ$22,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I23I23=INDEX('Data - Actuals'!$B$23:$BZ$23,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I24I24=INDEX('Data - Actuals'!$B$24:$BZ$24,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I25:K25I25=SUM(I20:I24)I26:K26I26=I12-I17-I25I27I27=INDEX('Data - Actuals'!$B$27:$BZ$27,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I28I28=INDEX('Data - Actuals'!$B$28:$BZ$28,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I29I29=INDEX('Data - Actuals'!$B$29:$BZ$29,1,MATCH($H$1,'Data - Actuals'!$B$1:$BZ$1,0))I30:K30I30=I26-I27-I28-I29Cells with Data ValidationCellAllowCriteriaH1:M1List=$N$1:$N$8


----------



## Fluff (Dec 21, 2022)

Instead of using EDATE, use
	
	
	
	
	
	



```
LEFT(TRIM(H1),4)&RIGHT(TRIM(H1),4)-1
```


----------



## Katich (Dec 21, 2022)

Fluff said:


> Instead of using EDATE, use
> 
> 
> 
> ...


That seemed to have worked!  Thank you!


----------



## Fluff (Dec 21, 2022)

You're welcome & thanks for the feedback.


----------



## Peter_SSs (Dec 21, 2022)

Katich said:


> That worked! Thank you!


The marked solution has been changed accordingly - as the solution to the original question.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.


----------

