Learn how to automatically include multiple sheet names in Excel formulas. Use the INDIRECT function to dynamically reference sheet names and efficiently look up and consolidate data across multiple sheets.
[Key Points]
When using the INDIRECT function with a sheet name in Excel, you must include the range to reference after the sheet name (for example, “!B2:B5”). If cell A1 contains the sheet name, use the following format.
INDIRECT(“‘” & A1 (sheet name)& “‘!B2:B5“)
This allows the INDIRECT function to correctly interpret the sheet name and range and reference the correct cells. This is important for dynamic references and preventing errors.
Function Overview: What Are INDEX, MATCH, and INDIRECT?
- INDEX function: Returns a value from a specified row and column in a range
- MATCH function: Finds the position of a specific value and returns its relative row number
- INDIRECT function: Converts text into a cell reference for dynamic lookups
Using INDEX and MATCH on a Single Sheet
Data Table (Sheet: Sales_2023)
| Column B (Product) | Column C (Region) | Column D (Sales) |
|---|---|---|
| A | North | 100 |
| A | South | 150 |
| B | North | 200 |
| B | South | 250 |
Formula: =INDEX(D2:D5, MATCH(1, (B2:B5 = "A") * (C2:C5 = "North"), 0))
Result: Sales value = 100
Expand to Multiple Sheets: Add INDIRECT
Summary Sheet Setup
| Column A (Item) | Column B (Value) |
|---|---|
| Sheet Name | Sales_2023 |
| Product | A |
| Region | North |
| Sales | 100 |
Formula: =INDEX(INDIRECT("'" & B1 & "'!D2:D5"), MATCH(1, (INDIRECT("'" & B1 & "'!B2:B5") = B2) * (INDIRECT("'" & B1 & "'!C2:C5") = B3), 0))
Precautions and Practical Tips
- Sheet name accuracy: Watch for capitalization and spaces
- Error handling: Improve reliability with IFERROR
- Performance optimization: For large datasets, consider using tables