Lookup and Reference
The INDIRECT function in Excel is used to convert a text string into a valid cell reference. It can be used to dynamically reference a cell or range of cells by providing the cell address as a string. For example, if you have the text "A2" in cell B2, you can use the formula =INDIRECT(B2) in cell C2 to reference the value in cell A2. The INDIRECT function is often used in combination with other functions, such as the SUM or COUNTIF functions, to create dynamic references to data in a spreadsheet.
It is useful and frequently used combined with other functions.
I've personally used this function the other day for my freelance service to a client together with ROW and COLUMN functions. I needed to locate a value of cell that is equal to the value that I am looking up in a range.
Imagine using VLOOKUP, but in reverse.
Another use for INDIRECT function is to setup a multi-dependent dropdown.
I'll make a blogpost about this as well and link it here once available.
=INDIRECT(ref_text, [a1])
ref_text - A reference supplied as text. to a cell or range of cells.
[a1] - This is a boolean (yes or no) argument to indicate if we want A1 or R1C1 style. A1 style means the column is alphabet. R1C1 style means that column is indicated by column number.
Square bracket around a1 means that this is an optional argument and it will use A1 style when ommitted.
In the example above, we are asking Excel to use the reference of whatever is in cell B2. Since B2 has the value A2, our INDIRECT function returns =A2 so it returns whatever is the value in cell A2.
Suppose there are different values in cell A2 of different sheets and we want to add them.
In the image below, Column C is what we will type in the indirect function. We can just type in the formula in cell C3, then we can copy and paste it up to cell C5.
We combined the text in B3 with the text string "!A2" to come up with the reference =Sheet1!A2.
Column D is what they would look like if typed directly.
Still need help with Excel and Google Sheets?
Get Instant Expert Help in Fiverr or PeoplePerHour platforms. Click one icon below.
We can also accept LEO, HIVE, HBD tokens as payment if we transact directly here in HIVE network.
This post is designated as a reviewer for Excel trainings I conduct.
Feel free to share with anyone. 馃槈
Earnings from Upvotes on this post are used to continue sharing Excel knowledge.
If you also want to earn HIVE in 7 days while blogging your favorite topics, join us here at Peakd.
*Thumbnail used is made in Canva.
