INDIRECT() Function in Excel: A Beginner's Guide
Overview of the
INDIRECT() Function
The INDIRECT()
function in Excel is used to create a cell reference from a text string. This
allows users to dynamically refer to different cells or ranges based on the
contents of other cells. It’s particularly useful for creating flexible
formulas and for referencing cells in different sheets or workbooks without
hardcoding the references.
Syntax
The syntax of the
INDIRECT() function is as follows:
INDIRECT(ref_text, [a1])
- ref_text: A
text string that specifies the cell reference. This can be a single cell,
a range of cells, or a reference to a named range.
- a1 (optional):
A logical value that specifies the reference style:
- TRUE
or omitted: The reference is interpreted as an A1-style reference (e.g.,
"A1", "B2").
- FALSE:
The reference is interpreted as an R1C1-style reference (e.g.,
"R1C1", "R2C2").
How INDIRECT() Works
The INDIRECT()
function is unique because it allows you to construct references using text.
This means you can build dynamic references that change based on the data in
your worksheet. For example, if you have a cell that specifies the row or
column number you want to reference, you can use INDIRECT() to create a
reference to that cell.
Example Scenario
Let’s consider a
practical example to illustrate how to use the INDIRECT() function effectively.
Sample Data
Assume you have sales
data for different products in two separate sheets, and you want to create a
summary sheet that references these sheets based on user input.
1.
Sales Sheet 1 ("Sales_Q1"):
|
Product |
Sales |
|
Product A |
2000 |
|
Product B |
3000 |
|
Product C |
2500 |
2.
Sales Sheet 2 ("Sales_Q2"):
|
Product |
Sales |
|
Product A |
2500 |
|
Product B |
3500 |
|
Product C |
2800 |
Steps to Use
INDIRECT()
1.
Create a Summary Sheet: In the summary sheet, you can have the following setup:
|
Quarter |
Product |
Sales |
|
Q1 |
Product A |
|
|
Q1 |
Product B |
|
|
Q1 |
Product C |
|
|
Q2 |
Product A |
|
|
Q2 |
Product B |
|
|
Q2 |
Product C |
2.
Use INDIRECT to Reference Sales:
In the Sales column
of the summary sheet, you can use the INDIRECT() function to pull sales data
from the respective quarter sheets.
For example, to get
the sales for Product A in Q1, you can use the
following formula in cell C2:
=INDIRECT("Sales_Q1!B" & MATCH(B2, INDIRECT("Sales_Q1!A:A"), 0))
Explanation:
o
"Sales_Q1!B" specifies the
column for sales in the "Sales_Q1" sheet.
o
MATCH(B2,
INDIRECT("Sales_Q1!A:A"), 0) finds the row number for "Product
A" in the range "A
" of the
"Sales_Q1" sheet.
o
The INDIRECT() function combines these
to create a dynamic reference to the correct sales figure.
3.
Copy the Formula for Other Products: You can then drag the formula down to fill in sales for other products
in Q1.
4.
For Q2: To reference sales for Q2, you can modify the formula in C4 (for
Product A in Q2) to:
=INDIRECT("Sales_Q2!B" & MATCH(B4, INDIRECT("Sales_Q2!A:A"), 0))
Important Notes
1.
Dynamic References: INDIRECT() is especially useful for creating formulas that can adapt
to changes in the workbook, such as when adding or removing data.
2.
Volatile Function: INDIRECT() is a volatile function, meaning it recalculates whenever
any change is made in the workbook. This can lead to performance issues in
large spreadsheets.
3.
Error Handling: If the reference specified in ref_text does not exist or is invalid,
Excel will return a #REF! error. Always ensure that your text string correctly
matches the intended cell or range.
4.
Referencing Different Worksheets: You can use INDIRECT() to reference cells in other worksheets by using
the format "SheetName!CellReference".
Practical
Applications
1.
Dynamic Reports: Use INDIRECT() to create reports that pull data from various sheets
based on user inputs or selections.
2.
Flexible Models: In financial models, INDIRECT() can help in creating scenarios where
data can be pulled from various sheets dynamically.
3.
Dashboards: Build dynamic dashboards that reference different datasets based on
user selection, allowing for interactive data exploration.
Conclusion
The INDIRECT() function is a powerful and flexible tool in Excel that allows users to create dynamic references based on text strings. By understanding its syntax and applications, MBA students can effectively utilize this function for various data analysis and reporting tasks.
Assignment 1: Dynamic
Sales Report
Task: Create a dynamic sales report that references sales data from
different quarterly sheets based on user input.
Sample Data
1.
Sales Sheet 1 ("Sales_Q1"):
|
Product |
Sales |
|
Product A |
2000 |
|
Product B |
3000 |
|
Product C |
2500 |
2.
Sales Sheet 2 ("Sales_Q2"):
|
Product |
Sales |
|
Product A |
2500 |
|
Product B |
3500 |
|
Product C |
2800 |
3.
Sales Sheet 3 ("Sales_Q3"):
|
Product |
Sales |
|
Product A |
2200 |
|
Product B |
3300 |
|
Product C |
2700 |
Summary Sheet Layout
|
Quarter |
Product |
Sales |
|
Q1 |
Product A |
|
|
Q1 |
Product B |
|
|
Q1 |
Product C |
|
|
Q2 |
Product A |
|
|
Q2 |
Product B |
|
|
Q2 |
Product C |
|
|
Q3 |
Product A |
|
|
Q3 |
Product B |
|
|
Q3 |
Product C |
Instructions
1.
In the Sales column,
use the INDIRECT() function to pull sales data from the appropriate quarterly
sheet based on the values in the Quarter column.
2.
Use the following formula in cell C2
(for Q1 Product A):
=INDIRECT("Sales_" & A2 & "!B" & MATCH(B2, INDIRECT("Sales_" & A2 & "!A:A"), 0))
Solution Steps
1.
Formula for Q1 Product A (Cell C2):
=INDIRECT("Sales_" & A2 & "!B" & MATCH(B2, INDIRECT("Sales_" & A2 & "!A:A"), 0))
o
Result: 2000
2.
Copy the formula down for all products
in Q1. The values for Products B and C will be 3000 and 2500,
respectively.
3.
For Q2 Products: In cell C5 (for Q2 Product A), use:
=INDIRECT("Sales_" & A5 & "!B" & MATCH(B5, INDIRECT("Sales_" & A5 & "!A:A"), 0))
o
Result: 2500 for
Product A in Q2, and so on for Products B and C.
4.
For Q3 Products: Repeat the same process, referencing the Q3 data.
Assignment 2: Grade
Summary
Task: Create a summary of student grades from different subjects.
Sample Data
1.
Math Grades ("Math_Grades"):
|
Student |
Grade |
|
John |
85 |
|
Alice |
90 |
|
Mark |
78 |
2.
Science Grades ("Science_Grades"):
|
Student |
Grade |
|
John |
82 |
|
Alice |
95 |
|
Mark |
88 |
3.
English Grades ("English_Grades"):
|
Student |
Grade |
|
John |
89 |
|
Alice |
92 |
|
Mark |
75 |
Summary Sheet Layout
|
Subject |
Student |
Grade |
|
Math |
John |
|
|
Math |
Alice |
|
|
Math |
Mark |
|
|
Science |
John |
|
|
Science |
Alice |
|
|
Science |
Mark |
|
|
English |
John |
|
|
English |
Alice |
|
|
English |
Mark |
Instructions
1.
In the Grade column,
use the INDIRECT() function to pull grades from the appropriate subject sheet
based on the values in the Subject column.
2.
Use the following formula in cell C2
(for Math John):
=INDIRECT(A2 & "_Grades!B" & MATCH(B2, INDIRECT(A2 & "_Grades!A:A"), 0))
Solution Steps
1.
Formula for Math John (Cell C2):
=INDIRECT(A2 & "_Grades!B" & MATCH(B2, INDIRECT(A2 & "_Grades!A:A"), 0))
o
Result: 85
2.
Copy the formula down for all students
in Math. The values for Alice and Mark will be 90 and 78,
respectively.
3.
For Science Grades: In cell C5 (for Science John), use:
=INDIRECT(A5 & "_Grades!B" & MATCH(B5, INDIRECT(A5 & "_Grades!A:A"), 0))
o
Result: 82 for John in
Science.
4.
Repeat for English: Use the same process to reference grades from the English sheet.
Assignment 3:
Employee Salary
Task: Create a salary report that references salary data from different
departments.
Sample Data
1.
Sales Salaries ("Sales_Salaries"):
|
Employee |
Salary |
|
John |
50000 |
|
Alice |
60000 |
|
Mark |
55000 |
2.
IT Salaries ("IT_Salaries"):
|
Employee |
Salary |
|
Sarah |
70000 |
|
Bob |
80000 |
|
Charlie |
75000 |
3.
HR Salaries ("HR_Salaries"):
|
Employee |
Salary |
|
Anna |
65000 |
|
Tom |
72000 |
|
Julia |
68000 |
Summary Sheet Layout
|
Department |
Employee |
Salary |
|
Sales |
John |
|
|
Sales |
Alice |
|
|
Sales |
Mark |
|
|
IT |
Sarah |
|
|
IT |
Bob |
|
|
IT |
Charlie |
|
|
HR |
Anna |
|
|
HR |
Tom |
|
|
HR |
Julia |
Instructions
1.
In the Salary column,
use the INDIRECT() function to pull salary data from the appropriate department
sheet based on the values in the Department column.
2.
Use the following formula in cell C2
(for Sales John):
=INDIRECT(A2 & "_Salaries!B" & MATCH(B2, INDIRECT(A2 & "_Salaries!A:A"), 0))
Solution Steps
1.
Formula for Sales John (Cell C2):
=INDIRECT(A2 & "_Salaries!B" & MATCH(B2, INDIRECT(A2 & "_Salaries!A:A"), 0))
o
Result: 50000
2.
Copy the formula down for all employees
in Sales. The values for Alice and Mark will be 60000 and 55000,
respectively.
3.
For IT Salaries: In cell C5 (for IT Sarah), use:
=INDIRECT(A5 & "_Salaries!B" & MATCH(B5, INDIRECT(A5 & "_Salaries!A:A"), 0))
o
Result: 70000 for
Sarah.
4.
Repeat for HR: Use the same process to reference salaries from the HR sheet.
Conclusion
