Welcome back! In our last lesson, you mastered XLOOKUP, the modern, flexible tool for retrieving data in Excel. You saw how it easily handles lookups to the left and has safer, more intuitive defaults than its predecessors.
While XLOOKUP is the future, in any MIS role, you will inevitably encounter spreadsheets built before it existed. For years, the gold standard for powerful and flexible lookups was the combination of the INDEX and MATCH functions. Understanding how they work together is a crucial skill for reading, maintaining, and debugging existing reports.
Today, we will focus on this lesson's learning outcome: Apply INDEX and MATCH functions together as a robust alternative for two-way lookups. We'll break down each function individually and then combine them to perform lookups that find data at the intersection of a specific row and column.
1. The Building Blocks: INDEX and MATCH
Before combining them, it's essential to understand what each function does on its own. Think of them as a team: INDEX is the tool that can retrieve an item, but it needs MATCH to tell it where to look.
The INDEX Function: Your Data "Map"
The INDEX function returns a value from a specific location within a table or range (which we'll call an "array"). You can think of it like the game of Battleship: you give it the game board (the array) and a set of coordinates (a row and column number), and it tells you what's in that cell.
The syntax is:=INDEX(array, row_num, [column_num])
array: The range of cells that contains the potential answers. This is your "map" or "game board."row_num: Which row to look in.column_num: Which column to look in.
Let's see this explained visually.
This video from Kevin Stratvert uses the perfect 'Battleship' analogy to explain how INDEX works. It's a very clear way to understand its basic purpose.
Watch from 01:05 to 03:32. Notice how you must provide the array (the board) and the exact row and column numbers to get the value.
On its own, INDEX isn't very dynamic. You usually don't know that the data you need is in "row 5, column 3." That's where MATCH comes in.
The MATCH Function: Finding the Coordinates
The MATCH function does the opposite of a lookup. Instead of finding a value, it finds the position of a value within a list. It searches for an item in a single row or column and tells you which number position it's in (e.g., the 1st, 5th, 10th item). This provides the dynamic row_num and column_num that INDEX needs.
The syntax is:=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: The value you are searching for.lookup_array: The single column or row to search within.match_type:0for an exact match. This is what you'll use almost all the time.
How to use Excel Index Match (the right way)
Now, let's look at the MATCH function. This segment from a video by Leila Gharani clearly explains its purpose and syntax.
Watch from 04:54 to 06:27. Pay close attention to how MATCH returns a number (the position), not the value itself. Also note the key rule: the lookup_array for MATCH must be a single row or column (a 'one-way street').
2. Putting Them Together: The Two-Way Lookup
Now we get to the core of this lesson. A two-way (or matrix) lookup allows you to find a value at the intersection of a specific row and a specific column. This is extremely common in MIS reports, such as finding sales for a certain product in a specific month, or, in your case, finding the number of incidents of a particular type for a given quarter.
To do this, we nest two MATCH functions inside one INDEX function:
- One
MATCHto find the correct row number. - Another
MATCHto find the correct column number.
The formula structure looks like this:
=INDEX(data_array, MATCH(row_lookup, row_headers_array, 0), MATCH(column_lookup, column_headers_array, 0))
Let's break this down:
INDEX(data_array, ...): Thearrayis now the entire block of data values, not including the row or column headers.MATCH(row_lookup, ...): This finds the row number you need. For example, looking for "Phishing" in the list of incident types.MATCH(column_lookup, ...): This finds the column number you need. For example, looking for "Q2" in the list of quarters.
To see exactly how to build this formula step-by-step, we'll watch a clear demonstration.
How to use Excel Index Match (the right way)
This clip from Leila Gharani's tutorial expertly demonstrates how to build a two-way lookup from scratch.
Watch from 08:07 to 10:31. Notice how she first extends the INDEX array to cover all possible answers, and then adds a second MATCH function to the column_num argument to make the column lookup dynamic.
For a slightly different perspective and to reinforce the concept, this article provides excellent written examples.
INDEX & MATCH Functions Combo in Excel (10 Easy Examples)
The article 'INDEX & MATCH Functions Combo in Excel' from TrumpExcel.com provides a great set of practical examples. We will focus on the two-way lookup.
Read 'Example 3: Two Way Lookup'. This will give you a static reference for the formula structure we just saw in the video. Pay attention to how the row and column MATCH functions are constructed.
3. Why Learn This if XLOOKUP Exists?
This is a fair question. While XLOOKUP is simpler, INDEX/MATCH is a critical skill for a few key reasons:
- Legacy Systems: A huge number of existing MIS reports and financial models were built using
INDEX/MATCH. You need to be able to understand, troubleshoot, and modify these formulas. - Compatibility:
XLOOKUPis only available in newer versions of Excel (Microsoft 365, Excel 2021). If you or a colleague are using an older version like Excel 2016 or 2019,XLOOKUPwill not work.INDEX/MATCHworks in virtually all versions, making it universally compatible in corporate environments.
Think of it like learning to drive both an automatic and a manual car. The automatic (XLOOKUP) is easier and more common now, but knowing how to drive a manual (INDEX/MATCH) makes you a more versatile and capable driver, able to handle any car you're given.
4. Hands-On Practice
Let's apply this to a scenario from your cybersecurity background. Imagine you have a summary table of incidents per quarter.
| Incident Type | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Phishing | 150 | 185 | 160 | 210 |
| Malware | 45 | 30 | 55 | 40 |
| Data Leak | 12 | 15 | 8 | 18 |
| Ransomware | 5 | 2 | 7 | 4 |
You also have two cells elsewhere on your sheet for user input:
- Cell
G2: ContainsMalware - Cell
H2: ContainsQ3
Your Task:
In cell I2, write a single INDEX/MATCH formula that looks up the values in G2 and H2 to find the correct number of incidents from the table. For the data above, the correct answer is 55.
Hint: Remember to use absolute references ($) for your arrays if you were planning to copy this formula.
Click here to see the solution formula
Assuming your table is in the range A1:E5:
- The main data array (the numbers) is in
$B$2:$E$5. - The row headers (incident types) are in
$A$2:$A$5. - The column headers (quarters) are in
$B$1:$E$1.
The formula in cell I2 would be:=INDEX($B$2:$E$5, MATCH(G2, $A$2:$A$5, 0), MATCH(H2, $B$1:$E$1, 0))
Key Takeaways
INDEXretrieves a value from a grid using numerical row and column coordinates.MATCHfinds the numerical position of an item in a list, providing the coordinates forINDEX.- Combining them
(INDEX/MATCH/MATCH)creates a powerful two-way lookup to find data at the intersection of a row and column. - This skill is essential for compatibility with older Excel versions and for maintaining legacy reports you'll encounter as an MIS Executive.
Next Lesson
Whether you use VLOOKUP, XLOOKUP, or INDEX/MATCH, you will eventually try to look up a value that doesn't exist, resulting in an #N/A error. In our next lesson, we will learn how to gracefully manage these situations by wrapping lookup formulas with IFERROR to handle and display clean results instead of errors, a crucial step in creating professional, user-friendly reports.