Excel Tips: Index Match Match
In this post, I’m going to go over Index Match Match, which is a very useful Excel formula that is used in both the Hotel Valuation Model and the Hotel Acquisition Model – The Basic Model. This formula allows you to hone in on a specific target cell and return its value when two criteria are met using both a column and header reference.
To elaborate, in our example, we will use a hotel proforma, which has line items on the left hand side, the years in the header, and a field of values in between. Using the years and the line items, our goal is to return the relevant values in each year for each line item on a sheet while ignoring all the other information present.
This is particularly useful in our hotel model because in it, we include additional columns in each year for percent of total revenue, which is common in hotel underwriting. And what we want to do is pull out the annual dollar amounts while ignoring the columns with percentage numbers so that we can see a quick annual cash flow on another sheet.
Why this formula is so useful is because we can simply type out the formula once, copy and paste it in the relevant cells, and that’s it. Without it, we would take much more time to complete this task.
Please check out the video below and download the template at the bottom.
Bonus Material and Primer – Index Match
If you have never used Index Match, or are not very familiar with it, start here before moving on to the Index Match Match lesson.
Back in the early days of A.CRE, I knew nothing about SEO and organizing a website for easy access, so I would not be surprised if no one saw this, but I actually do have a pretty decent tutorial on the Index Match function buried in a post titled Create A Dynamic Revenue Row to Calculate Multiple Tenant Leases. If you are unfamiliar with Index Match, then start here with a video from back in my primitive internet days.
Click Here to Download The Index Match Tutorial Template
Index Match Match – Video and Downloadable Excel File
Template file can be downloaded below
Click Here to Download The Tutorial Template
