The INDEX/MATCH/MATCH combination is a powerful tool for dynamically looking up values, offering greater flexibility and efficiency. Here’s how to use it in Excel and Google Sheets:
How INDEX and MATCH Work
INDEX(range, row_number, column_number) → Returns a value from a table at the specified row and column.
MATCH(lookup_value, lookup_range, match_type) → Finds the position of a value in a row or column.
Step-by-Step Guide: How to Use INDEX/MATCH/MATCH
Step 1: Use MATCH to Find the Row Number and Column Number
The MATCH function finds the row number where “Design Income” is and the column number where "Feb 2025" is located:
=MATCH(“Design Income”, B13:B25, 0)
"Design Income" → The row value you're searching for.
B13:B25 → The range where the value is located.
0 → Exact match mode.
=MATCH(“Feb 2025”, D12:G12, 0)
"Feb 2025" → The column value you're searching for.
D12:G12 → The range where the value is located.
0 → Exact match mode.
If "Design Income" is in the second row of this range, the result is 2. If "Feb 2025" is in the second column, the result is also 2.
Step 2: Use INDEX to Return the Desired Value
Now, nest the MATCH function inside INDEX to pull data from another column:
=INDEX(D13:G25, MATCH(“Design Income”, B13:B25, 0), MATCH("Feb 2025", D12:G12, 0))
D13:G25 → The table containing the values.
MATCH("Design Income", B13:B25, 0) → Finds the correct row where the account is Design Income.
MATCH("Feb 2025", D12:G12, 0) → Finds the correct column where the date is Feb 2025.
If entered correctly, the formula will return 21,375, which is the account value for Design Income in Feb 2025.
Common Mistakes & How to Fix Them
❌ #N/A Error – The lookup value doesn’t exist or isn’t an exact match (check for typos).
❌ Wrong Column Index – Ensure the column number in INDEX matches the data range.
❌ Using Approximate Match Unintentionally – Always set the last argument in MATCH to 0 for exact matches.
Incorporating Named Ranges into the INDEX/MATCH/MATCH Function
Using named ranges in INDEX/MATCH/MATCH improves formula readability and makes it easier to manage large datasets. Named ranges help avoid hardcoded cell references, making formulas more intuitive and easier to maintain.
To learn more about named ranges, check out the Guide to Named Ranges in LiveFlow.
The general syntax for the INDEX/MATCH/MATCH function using named ranges is:
INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, {SheetName}_ColumnLabels, 0))
Using the example above, we can input our named ranges as follows:
=INDEX(ProfitLoss_Contents,MATCH("Design Income", ProfitLoss_RowLabels, 0), MATCH("Feb 2025", ProfitLoss_ColumnLabels, 0))
"ProfitLoss_Contents" → The table containing the values (D13:G25).
MATCH("Design Income", ProfitLoss_RowLables, 0) → Finds the correct row where the account is Design Income (B13:B25).
MATCH("Feb 2025", ProfitLoss_ColumnLables, 0) → Finds the correct column where the date is Feb 2025 (D12:G12)
The formula will return 21,375, which is the Design Income for February 2025.
Why Use Named Ranges?
✅ Improves readability – No need for complex cell references.
✅ Easier maintenance – If the data expands, just update the named range.
✅ More flexibility – Works well in dynamic reports and dashboards.