To get the values from a cell in Excel, you can use two different methods. In these methods, you need to write a quick formula to get the value. In this tutorial, we will learn to write these formulas:
Use INDEX to Get the Value from a Cell by Referring to a Range
If you have a range of cells and you want to get the value of the cell from a particular cell from that range. With the INDEX function, you can specify a range and use the index number and the function will return the value.
In the following example, we have the four values in the range A1:A4. And you need to get the values from the second cell of the range. For this you can use the INDEX function in the following way:
=INDEX(A1:A4,2)
Once you hit enter, it returns the value “TWO” from the second cell of the range which is A2.
Use a Combination of INDIRECT and ADDRESS
You can also use the combination of the INDIRECT and ADDRESS functions:
- INDIRECT: It allows you to get the cell value by specifying the address of the cell. You can use both A1 and R1C1 reference styles.
- ADDRESS: It allows you to create a cell address using the row and column number.
Now let’s combine these two functions:
- First, enter the indirect function in the cell.
- Next, enter the address function.
- Now, in the address function enter the row number and the column number.
- In the end, close the formula and hit enter to get the result.
And once you hit enter, it will return the value from the cell which is in the 2nd row and the 1st column (A1).
To understand this formula, you need to split it into two parts:
First Part: As we have discussed earlier, ADDRESS creates a cell address based on the row and column number you have specified.
Second Part: By using the cell address returned by the ADDRESS function, the INDIRECT function returns the value from the cell.
Note: INDIRECT is a volatile function, that updates its value whenever you update anything in your worksheet. This is good to always have the updated value from the cell, but it also makes Excel a little slower.