Index/Match Formula?

K

Katich

Board Regular
Joined
Jan 22, 2008
Messages
154
Office Version
  1. 2019
Platform
  1. Windows
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.xlsx
ABCDEF
1January-22
2 ACT BUD PRIOR BUD VAR PY VAR
3 Rooms Available 4,920#N/A4,920#N/A0
4 Rooms Sold 2,403#N/A2,403#N/A0
5 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/A0
10 Food & Beverage Revenue 67,351#N/A67,351#N/A0
11 Other Minor Operated Depts. Rev. 4,330#N/A4,330#N/A0
12 Total Operating Revenue 224,616#N/A224,616#N/A0
13 Departmental Expenses
14 Rooms Expenses 74,131#N/A74,131#N/A0
15 Food & Beverage Expenses 96,284#N/A96,284#N/A0
16 Other Minor Oper. Depts. Expenses 1,852#N/A1,852#N/A0
17 Total Departmental Expenses 172,267#N/A172,267#N/A0
18 Total Departmental Profit 52,349#N/A52,349#N/A0
19 Undistributed Operating Expenses
20 Administrative & General 64,709#N/A64,709#N/A0
21 Information & Telecomm. Systems 9,304#N/A9,304#N/A0
22 Sales & Marketing 45,557#N/A45,557#N/A0
23 Property Operations & Maint. 25,859#N/A25,859#N/A0
24 Utilities/Franchise Royalty Fees 9,969#N/A9,969#N/A0
25 Total Undistributed Expenses 155,398#N/A155,398#N/A0
26 Gross Operating Profit (103,049)#N/A(103,049)#N/A0
27 Management Fees 5,615#N/A5,615#N/A0
28 Propert & other Taxes/Insurance 16,859#N/A16,859#N/A0
29 FF&E Reserve 0#N/A0#N/A0
30 EBITDA (125,523)#N/A(125,523)#N/A0
DOUBLETREE HOTEL- Tracker
Cell Formulas
RangeFormula
B3,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-C3
F3:F7,F20:F30,F14:F18,F9:F12F3=B3-D3
B4,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-B17
B20,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-B25
B27,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-B29
Named Ranges
NameRefers ToCells
'DOUBLETREE HOTEL- Tracker'!Print_Area='DOUBLETREE HOTEL- Tracker'!$A$1:$M$34B3:D7, B9:D11, B14:D16, B20:D24, B27:D29
Cells with Data Validation
CellAllowCriteria
A1:F1List='Data - Actuals'!$B$1:$AN$1
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Maybe
Excel Formula: 
=INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH(EDATE($A$1,-12),'Data - Actuals'!$B$1:$BZ$1,0))
 
Upvote 0
Solution
Fluff said:
Maybe
Excel Formula: 
=INDEX('Data - Actuals'!$B$2:$BZ$2,1,MATCH(EDATE($A$1,-12),'Data - Actuals'!$B$1:$BZ$1,0))
Click to expand...
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.xlsx
HIJKLM
1 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- Tracker
Cell Formulas
RangeFormula
I3I3=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-J3
M3:M7,M20:M30,M14:M18,M9:M12M3=I3-K3
I4I4=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-I17
I20I20=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-I25
I27I27=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-I29
Cells with Data Validation
CellAllowCriteria
H1:M1List=$N$1:$N$8
 
Upvote 0
Instead of using EDATE, use
Excel Formula: 
LEFT(TRIM(H1),4)&RIGHT(TRIM(H1),4)-1
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Katich said:
That worked! Thank you!
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Opening of excel file takes to much time
Replies
6
Views
201
Fluff
Fluff
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
107
strugglingaccountant
S
Y
  • Question
Mulitple index and match
Replies
1
Views
244
etaf
etaf
szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
314
Alex Blakenburg
A
SFCChase
  • Question
Index, Match limitations
Replies
9
Views
751
StephenCrump
S

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top