How to Use INDEX MATCH in Excel to Build a Reporting Template
- TWX Margerate
- Jul 2
- 7 min read
Many Excel tutorials explain INDEX MATCH using simple examples like looking up a product price or employee name.
Those examples are useful for learning the formula.
But in real work, lookup formulas are often used for something more practical:
turning exported data into a report format that people actually need.
In this tutorial, I’ll show how INDEX MATCH can be used to build a reusable reporting template in Excel.
The example is based on a common workplace situation:
You export a full summarized report from a system.
The export contains many months, regions, channels, and metrics.
But your manager only needs selected KPIs in a clean report format.
Instead of manually finding, copying, and pasting the numbers every month, you can use INDEX MATCH to pull the required values automatically.
Watch the Video Tutorial
I also created a video walkthrough for this example.
In the video, I first show the formula using normal cell ranges, then I show how the same logic can be applied using Excel Tables and structured references.
You can watch the full video here:
Download the Practice Files
You can download the practice files and follow along with the tutorial.
I included two versions:
1. Range Version
Use this version if you want to understand the basic INDEX MATCH logic using normal cell references.
Download:
2. Table Version
Use this version if you want to practise a more structured reporting-template version using Excel Tables and structured references.
Download:
If you are new to INDEX MATCH, I recommend starting with the Range Version first.
Once the logic makes sense, you can move to the Table Version.
The Business Scenario
Imagine you export a summarized report from a system.
The system export may already contain the full summarized view of the business, including metrics such as:
Revenue
Gross Sales
Discounts
Net Revenue
Cost
Gross Profit
Gross Margin %
Orders
Units Sold
Average Order Value
New Customers
Repeat Customers
Return Amount
Return Rate %
Marketing Spend
Customer Acquisition Cost
Conversion Rate %
On-time Delivery %
Customer Satisfaction Score
This export is complete, but it may contain more information than your manager needs.
For example, your manager’s monthly report may only need selected KPIs, such as:
Revenue
Gross Profit
Gross Margin %
Orders
Conversion Rate %
On-time Delivery %
So the problem is not that the data is missing.
The problem is that the exported report has too much information, while the manager report needs a specific layout.
This is where a reusable reporting template can help.
Instead of copying numbers manually from the system export into the report every month, you can create a template that pulls the required numbers using INDEX MATCH.
When This Approach Makes Sense
This approach is useful when:
the system export is already summarized
the report format is fixed
you only need selected KPIs
you prepare the same report repeatedly
you want to reduce manual copy-paste work
you want the report to update when the selected month changes
This can be useful for:
monthly sales reports
KPI reports
operations reports
management reports
team performance reports
recurring business review templates
This is not meant to replace PivotTables, Power Query, or Power BI.
If you need to clean raw data, combine multiple files, transform data, analyze detailed transactions, or build a dynamic dashboard, those tools may be better.
But if the export is already summarized and your goal is to populate a fixed report layout, INDEX MATCH can be a practical solution.
The Core Idea of INDEX MATCH
Before going into the reporting example, let’s understand the basic logic.
INDEX and MATCH work together.
MATCH finds the position.
INDEX returns the value.
That is the most important idea.
What INDEX Does
The INDEX function returns a value based on position.
For example:
=INDEX(D2:D6,3)
This tells Excel to look at the range D2:D6 and return the 3rd value from that range.
If the 3rd value is 120, the formula returns 120.
So the role of INDEX is:
Return the value from this position.
What MATCH Does
The MATCH function finds the position of an item.
For example:
=MATCH("North",A2:A6,0)
This tells Excel to find North inside the range A2:A6.
If North is the first item in the range, MATCH returns 1.
The 0 means exact match.
So the role of MATCH is:
Find where the item is.
MATCH does not return the final value. It only returns the position.
How INDEX MATCH Works Together
When we combine INDEX and MATCH, MATCH finds the position, and INDEX returns the value from that position.
Example:
=INDEX(D2:D6,MATCH("North",A2:A6,0))
Here is what happens:
MATCH("North",A2:A6,0) finds the position of North.
INDEX(D2:D6,...) returns the value from the same position in D2:D6.
So the formula logic is:
Find the matching row, then return the value from that row.
Why We Need Two-Way INDEX MATCH for the Report Template
In this reporting example, we are not only looking up one value from one column.
The manager report needs values based on both:
the correct row
the correct KPI column
For example, the report may need the Revenue for:
Region: North
Channel: Online
Month: May
Then it may need the Gross Profit for the same row.
Then it may need the Conversion Rate % for the same row.
This is why two-way INDEX MATCH is useful.
One MATCH finds the row.
Another MATCH finds the column.
INDEX returns the value where the row and column meet.
Why We Use a Helper Row Key
In the system export, each row is based on a combination of:
Region
Channel
Month
For example:
Region | Channel | Month |
North | Online | May |
North | Retail | May |
South | Online | May |
South | Retail | May |
If we only match by Region, it will not work because North appears more than once.
If we only match by Month, it will also not work because May appears many times.
So we create a Helper Row Key by combining Region, Channel, and Month.
For example:
=[@Region]&[@Channel]&[@Month]
This creates a unique key such as:
NorthOnlineMay
The same Helper Row Key is used in both:
the system export
the manager report template
This gives Excel a clear way to find the correct row.
In a real report, you can hide the Helper Row Key column after the report is built.
Simple Formula Structure
Before looking at the full formula, it helps to understand the simple structure:
=INDEX(data_range, MATCH(row_key, row_key_range, 0), MATCH(kpi_header, header_range, 0))
This formula does three things:
The first MATCH finds the correct row.
The second MATCH finds the correct KPI column.
INDEX returns the value at the intersection.
This is the logic behind the report template.
Range Version Formula
In the Range Version file, the formula uses normal cell references.
It may look something like this:
=IFERROR(INDEX(System_Export!$A:$W,MATCH(Manager_Report_Template!$J7,System_Export!$A:$A,0),MATCH(Manager_Report_Template!D$6,System_Export!$4:$4,0)),"Not Found")
The structure is:
INDEX(data_range, row_position, column_position)
The first MATCH finds the row:
MATCH(Manager_Report_Template!$J7,System_Export!$A:$A,0)
The second MATCH finds the column:
MATCH(Manager_Report_Template!D$6,System_Export!$4:$4,0)
Then INDEX returns the value where the matched row and column meet.
This version is useful for learning the logic because the cell references are easier to see.
Table Version Formula
In the Table Version file, the formula uses Excel Tables and structured references.
It may look longer, but the logic is the same:
=IFERROR(INDEX(SystemExport,MATCH(ManagerReportTemplate[@[Row Key]:[Row Key]],SystemExport[[Row Key]:[Row Key]],0),MATCH(D$6,SystemExport[#Headers],0)),"Not Found")
The formula still does the same three things:
Finds the correct row using the Row Key.
Finds the correct column using the KPI header.
Returns the value using INDEX.
The Table Version is useful because it is more structured and easier to maintain in a reusable reporting template.
For example, the table names make the formula more meaningful:
SystemExport
ManagerReportTemplate
Row Key
So although the formula may look longer, it is more descriptive.
Why the Formula Uses IFERROR
The formula is wrapped with IFERROR:
=IFERROR(...,"Not Found")
This keeps the report cleaner.
If Excel cannot find the Helper Row Key or the KPI header, it will show:
Not Found
instead of showing an Excel error.
This makes the report easier to review and troubleshoot.
Step-by-Step: How to Use the Practice Files
Range Version
Use this file if you are following the first demo in the video.
Open the Range Version file.
Go to the Manager_Report_Template sheet.
Change the report month.
Click one of the KPI formula cells.
Look at how the formula uses one MATCH for the row and another MATCH for the column.
Copy the formula across and down to see how the references work.
This version is best for understanding the formula logic.
Table Version
Use this file if you want to practise the structured reporting-template version.
Open the Table Version file.
Go to the Start_Here sheet to understand the scenario.
Go to the System_Export sheet to see the full summarized export.
Go to the Manager_Report_Template sheet.
Change the Report Month cell.
Watch the selected KPI report update automatically.
Click one of the KPI cells to see the structured reference formula.
Go to the Formula_Breakdown sheet if you want to understand each part of the formula.
This version is best for seeing how the formula can support a reusable reporting template.
Common Mistakes to Watch Out For
1. The Helper Row Key must be unique
If the same Helper Row Key appears more than once in the system export, MATCH will return the first matching row.
So make sure the Helper Row Key uniquely identifies each row.
2. Header names must match exactly
The report headers must match the system export headers.
For example:
Gross Margin %
and
GrossMargin%
are treated as different text.
If the header names are different, MATCH may not find the correct column.
3. Extra spaces can break the match
If one sheet has:
NorthOnlineMay
but another sheet has extra spaces, the formula may not match correctly.
So the Helper Row Key should be consistent in both sheets.
4. Cell references need to be locked correctly
In the Range Version, pay attention to the $ signs.
The row key reference should stay fixed when copying across.
The header reference should adjust correctly when copying across.
This is why understanding how to lock cell references is important when building report templates.
Why This Example Matters
The point of this tutorial is not just to learn a formula.
The point is to understand how a formula can support a real reporting workflow.
In many workplace reports, the challenge is not always that the data is missing.
Sometimes the data is already there, but it is not in the layout you need.
INDEX MATCH can help you build a bridge between:
the full system export
the clean manager report
This can reduce manual copy-paste work and make repeated reporting more consistent.
Final Recap
INDEX MATCH is useful because it helps Excel find and return values based on position.
The main idea is:
MATCH finds the position. INDEX returns the value.
For this reporting template:
One MATCH finds the correct row using the Row Key. Another MATCH finds the correct KPI column using the report header. INDEX returns the value where they meet.
Use the Range Version if you want to understand the basic formula logic.
Use the Table Version if you want to practise a more structured reporting-template setup.
This approach is especially useful when you already have a summarized system export and need to populate a fixed report format repeatedly.
Download the practice files, change the report month, and see how the report updates automatically.
Comments