Use the INDIRECT function in Excel It is something extremely simple. It is a search and reference function, the idea is to return the reference specified by a text string. We can use it when we want to change the cell reference within a formula without having to modify the formula in question.
We can’t deny that Microsoft’s office suite has a huge amount of possibilities, especially if we compare it with the free alternatives to Office. Interesting functions such as the possibility of automatically numbering rows, which will save us a lot of time; We can even create a pictogram or have 50 keyboard shortcuts with Ctrl that will also help us become more productive.
Use the INDIRECT function in Microsoft Excel
The first thing we will have to do is open Microsoft Excel and enter the data, we can also use existing data.
We will write in the cell where we want the results to appear =INDIRECT(A1) and we will press Enter to see the result that will be 42.
The result would be 42 because it refers to the value of column D1 which is 42, as we can see in the example.
Next, we are going to look up current sales using the R1C1-style benchmark method.
Where we want the result to appear, we will enter the following formula: =INDIRECT(“R7C”&COUNTA(7:7), FALSE).
The formula R represents the Row, which in this case is Row 7 and the C represents the column.
Here we are going to use the COUNTA function to count cells that have the data. We enter 7:7 into the formula because this is the row that has the information we want to find. Then we add False and press Enter to see the result.
Other methods
The first method is by clicking on the FX button that we can find in the left corner of the spreadsheet, at the top. We will see that a window called Insert function appears.
From here, we’re going to have to choose Select a Category and we’re going to choose Lookup & Reference from the list box.
In the Select a function section, we will choose INDIRECT and click OK. Next, a small Function Arguments window opens and we proceed to write in the input boxes the cell that has the reference we want to search for and click OK.
The second method is by clicking Formulas and then clicking the Lookup and Reference button found in the Function Library group.
After this, we will select INDIRECT in the drop-down menu, we will see that a Function Arguments dialog box opens, we will follow the steps discussed above and that’s it.
