The INDIRECT function can be used to return a reference to a range of cells based on a text string. In this blog post, we will take a closer look at how the INDIRECT function works and how it can be used to look up values in a named range and across different worksheets.
Using the INDIRECT function in Excel:
The INDIRECT function takes a single argument: a reference to a range of cells specified as a text string. The function then returns a reference to the range of cells specified by the text string.
How to use the INDIRECT Function
To use the INDIRECT function in Excel, start by opening a new worksheet. In any one of the cells, type the formula “=INDIRECT(reference_text)” and press enter. You should then see the cell’s value corresponding to the reference text.
For example, let’s say you have a value of “1200” in cell A2, and you want to use this cell as a reference to convert its value. To do this, you would write the formula “=INDIRECT(A2)” in any one of the cells. This would return the value “1200”, which is the value of the cell that is being referenced.
Using INDIRECT and Named Ranges with VLOOKUP
The VLOOKUP function with the INDIRECT function can be used to look up a value in a table by searching for a specific value in the first column and returning a value from a specified column in the same row.
Here are the steps to use the INDIRECT function with named ranges and the VLOOKUP function:
- Create named ranges for the tables that you want to reference in your VLOOKUP formula. In this example, we will use named ranges “Branch1” and “Branch2”.
- Use the INDIRECT function to reference the named range in the VLOOKUP formula. For example, if you want to reference the “Branch1” named range, the formula would be =VLOOKUP(F5,INDIRECT(“Branch1”),2,FALSE).
The F5 cell contains the product code, the second parameter of the INDIRECT function is the name of the named range, and the 2 is the column number. And including FALSE, at last, will give us the exact match.
Now you will get the value from the corresponding named range.
Worksheet Referencing with INDIRECT Function
Using the INDIRECT function with a worksheet reference is a way to reference a cell or range of cells on a different worksheet. This can be useful when you have multiple worksheets in a workbook, and you need to reference a cell or range of cells from another worksheet in a formula or function.
The formula for this is =INDIRECT(A2&”!A1″). It uses the INDIRECT function to reference a specific cell in a worksheet. The INDIRECT function takes a text string as an argument, a cell reference in the form of “sheet_name!cell_reference”.
In this example, cell A2 contains the name of a sheet, for example, “Rahul”. And the formula uses the ampersand operator (&) to concatenate the value of A2 and the text “!A1” to create the text string “Rahul!A1”. So the INDIRECT function will return the value of cell A1 in the sheet named Rahul.
Conclusion
The INDIRECT function is a powerful tool in Excel that allows you to reference named ranges in a more dynamic and flexible way. Instead of typing out cell references, you can use the INDIRECT function to reference named ranges within a formula, making your spreadsheet more organised and easier to maintain.
