1. How INDEX and MATCH Work Independently
Before combining them, it's essential to understand what each function does on its own. INDEX retrieves a cell value given a position number, while MATCH finds the position number given a search value!
1. INDEX() — THE FETCH CELL TOOLSyntax:
Syntax: =INDEX(array, row_num, [col_num])
Give INDEX a list or range of cells and tell it which row number to fetch.
=INDEX($A$2:$A$6, 3)Returns the item at Row 3 of Column A (e.g. "EMP-103").
2. MATCH() — THE POSITION COUNTERSyntax:
Syntax: =MATCH(lookup_value, array, [match_type])
Search a list for an item and return its 1-based index position number.
=MATCH("Rohan Mehta", $B$2:$B$6, 0)Searches Column B and returns 3 (because Rohan is row 3).
Formula Anatomy: The Combined INDEX MATCH Duo
=INDEX( $B$2:$E$5 , MATCH("East Region", $A$2:$A$5, 0) , MATCH("Q3 Sales", $B$1:$E$1, 0) )1. DATA MATRIX RANGE
Where is the data grid?
The matrix range containing values.
$B$2:$E$52. MATCH ROW INDEX
Which Row number?
Finds region row position.
MATCH(Region, $A$2:$A$5, 0)3. MATCH COLUMN INDEX
Which Column number?
Finds quarter column position.
MATCH(Quarter, $B$1:$E$1, 0)🎮 Interactive INDEX MATCH Simulator
Switch between 2D Matrix Grid Lookup Mode and 1D Left Lookup Mode to watch coordinates evaluate in real-time!
📊 Sales Data Matrix Range ($B$2:$E$5)
MATCH(Row: East Region) ✖ MATCH(Col: Q3 Sales)| Region Name | Q1 Sales | Q2 Sales | Q3 Sales 🎯 | Q4 Sales |
|---|---|---|---|---|
| North Region | $120,000 | $145,000 | $160,000 | $190,000 |
| South Region | $98,000 | $110,000 | $125,000 | $140,000 |
| East Region 🔍 | $135,000 | $150,000 | $175,000 | $210,000 |
| West Region | $155,000 | $170,000 | $185,000 | $230,000 |
INDEX MATCH INTERSECTION RESULT:$175,000
📊 Generated Excel Formula
=INDEX($B$2:$E$5, MATCH("East Region", $A$2:$A$5, 0), MATCH("Q3 Sales", $B$1:$E$1, 0))🧪 Knowledge Check — INDEX MATCH Quiz
Question 1 of 5Score: 0