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


About the Author: Michael has spent a decade working in various capacities on more than $7 billion of real estate transactions spanning all asset classes and geographies throughout the USA. Most recently, Michael was a founding member and COO of Stablewood Properties, an institutionally backed real estate operator. Before Stablewood, Michael was at Hines in San Francisco where he primarily worked on 2 high-rise mixed-use development projects totaling 2 million square feet.  Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.