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
Frequently Asked Questions about Excel’s Index Match Match Formula
What is Index Match Match in Excel?
Index Match Match is an Excel formula combination that returns a value from a table based on both a row and column criteria. It’s especially useful when you need to target a specific cell intersection, such as a value tied to a particular year and line item in a proforma.
How is Index Match Match used in hotel underwriting models?
In hotel models like the Hotel Valuation and Hotel Acquisition Models, the formula is used to extract dollar value data for specific line items (e.g., revenue or expenses) from a matrix of values. This helps isolate key cash flow items while ignoring percentage columns like “percent of total revenue.”
Why is Index Match Match better than other lookup methods for this task?
Unlike VLOOKUP or HLOOKUP, Index Match Match allows for flexible referencing in both rows and columns. Once set up, “we can simply type out the formula once, copy and paste it in the relevant cells, and that’s it,” saving time and improving accuracy.
What is the structure of an Index Match Match formula?
While not explicitly shown in the post, the formula generally follows this format:
=INDEX(array, MATCH(row_criteria, row_range, 0), MATCH(column_criteria, column_range, 0))
This returns the value located at the intersection of the specified row and column.
Is there a resource to learn Index Match before learning Index Match Match?
Yes. The post recommends starting with an earlier A.CRE tutorial titled “Create A Dynamic Revenue Row to Calculate Multiple Tenant Leases”, which includes a video and explanation on the basic Index Match function.
Where can I download the Index Match Match template?
You can download the Excel file with the Index Match Match tutorial directly from the blog post by clicking the link labeled “Click Here to Download The Tutorial Template.”
What makes this formula especially practical in financial modeling?
It automates the retrieval of specific values across dynamic ranges, reducing manual input and errors. This is especially useful in models that include both dollar values and percentages for the same time period, allowing users to focus only on annual cash flow data.
