1. What is the INDIRECT function?
The INDIRECT function is a built-in function in Microsoft Excel that allows users to refer to other cells or ranges textually. This means that you can dynamically refer to other cells or ranges in the workbook through a text string containing the cell reference. This capability is useful for tasks such as creating dynamic reports, referencing cells based on conditions, and simplifying formulas.
2. What is the function of the INDIRECT function?
The main purpose of the INDIRECT function is to make cell references dynamic and variable. Normally, formulas in Excel refer to cells directly, but sometimes we need to dynamically change the referenced cells at runtime. For example, the INDIRECT function comes in handy when you need to refer to different cells based on the user’s selection or certain conditions.
3. The basic syntax and parameters of the INDIRECT function are introduced in detail
The basic syntax of the INDIRECT function is as follows:
=INDIRECT(quoted text, [A1])in:
Reference Textis a text string containing a cell reference, either directly entered text, or a cell containing a cell reference.[A1]is an optional parameter that specifies the type of quoted text. IfTRUE(default), the quoted text is interpreted as an A1-style citation; ifFALSE, the quoted text is interpreted as an R1C1-style citation.
4. Give 3 examples of using the INDIRECT function, with tables
Example 1: Dynamic summation
Suppose we have a table as follows:
| A | B |
|---|---|
| Quantity | Price |
| 5 | 10 |
| 3 | 15 |
We want to dynamically calculate the sum of the quantity and price of the row based on the row number entered by the user. Using the INDIRECT function, you can enter the following formulas in cells C2 and C3 respectively:
In cell C2:
=SUM(INDIRECT("A" & D2), INDIRECT("B" & D2))In cell C3:
=SUM(INDIRECT("A" & D3), INDIRECT("B" & D3))In cells D2 and D3, the user enters the row number (2 or 3) to be summed, and the formula will dynamically calculate the sum of the quantity and price of the corresponding row according to the user’s input.
Example 2: Extract data from other workbooks
Suppose you have two workbooks: Workbook1 and Workbook2, and you want to extract data from Workbook2 and perform calculations in Workbook1. The following formulas can be used:
In Workbook1:
| A | B |
|---|---|
| value | calculation |
| 5 | =INDIRECT(“[Workbook2.xlsx]Sheet1!A1”) + A2 |
In Sheet1 of Workbook2:
| A |
|---|
| 10 |
In this example, the formula dynamically extracts the value of cell A1 (10) from Sheet1 of Workbook2 and calculates it.
Example 3: Conditional cell references
Suppose we have a simple grade table with student names (column A), subjects (column B) and grades (column C). We want to dynamically reference grades based on different subjects and student names.
| A | B | C |
|---|---|---|
| Name | Subject | Results |
| Zhang San | Mathematics | 98 |
| Li Si | Chinese | 90 |
| Wang Wu | English | 95 |
In cells E1 and E2, the user enters the required subjects and student names. In cell F1, enter the following formula to dynamically reference grades based on user input:
=INDIRECT("C" & MATCH(E2, A:A, 0))This formula will find a matching row in the Student Name column based on the entered student name and return the corresponding grade.
With these examples, you can see how to use the INDIRECT function to implement dynamic references in Excel, making your worksheets more flexible and adaptable to different situations. Depending on your needs, you can further explore and apply other uses of the INDIRECT function.