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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

C
  • Question Question
Pull First Text Value in a Column
Replies
6
Views
683
CrushFabrication
C
Tim_D
  • Solved
Is Vlookup and INDEX MATCH the best solution
Replies
3
Views
423
Fluff
Fluff
S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
240
Fluff
Fluff
D
  • Solved
index match multiple criteria
Replies
3
Views
305
Alex Blakenburg
A
N
  • Solved
Opening of excel file takes to much time
Replies
6
Views
432
Fluff
Fluff

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

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