How does indirect formula work in excel

Note : If either cell A2 or B2 is empty, the formula will return an error. To prevent this, you can add an IF function:. Next, follow these steps, to create a workbook that will link to the data workbook. Or, get the download file , and go to the WkbkRef sheet.

The formula will include the single quote marks, square brackets and exclamation mark. This would look like this:. In this, if you have a list of items such as a list of fruit for sale in the store, you can use the formula to find the location of a specific item. It will then place this information in the location specified in the row,col section of the formula. The function knows to go for C1 to A1 to get the number required and places this in cell D1. When you are creating your formula, you can opt for the A1 style or the R1C1 style.

For example, with the formula:. Therefore the formula will return the value in B2. There are lots of other ways to use the formula including with words as well as numbers with named ranges being one example. Before we move any further, you may want to know the difference between A1 and R1C1 reference styles. A1 style is the usual reference type in Excel that refers to a column followed by a row number.

For example, B2 refers to the cell at the intersection of column B and row 2. R1C1 style is the opposite reference type - rows followed by columns, which does take some time getting used to : For example, R4C1 refers to cell A4 which is in row 4, column 1 in a sheet. If no number comes after the letter, then you are referring to the same row or column.

As you see in the screenshot above, three different Indirect formulas return the same result. Have you already figured out why? I bet you have :. This is the easiest one. The formula refers to cell C1, fetches its value - text string A2 , converts it into a cell reference, heads over to cell A2 and returns its value, which is You can also utilize it to refer to cells in other worksheets "on the fly", and here's how.

Suppose, you have some important data in Sheet 1, and you want to pull that data in Sheet 2. The following screenshot demonstrates how an Excel Indirect formula can handle this task:. Formulas and functions. Need more help? Expand your skills. Get new features first. Was this information helpful?

Yes No. Thank you! Any more feedback? The more you tell us the more we can help.