To retrieve the values using references based on row & column numbers, we will use a combination of “INDIRECT”& “ADDRESS” functions to get the output.
INDIRECT: Returns the reference specified by a text string.
Example: If value in cell A1 contains 10, B1 contains A1 & we use INDIRECT function in cell C1=INDIRECT(B1), then result would be 10
ADDRESS: Creates a cell reference as text, given specified row & column numbers
Let us take an example:
Column A & B contain some random numbers. We need a formula to look for row number & column number & then find the value of that cell.
- In above shown example, if row is selected as 3 & column as 2 then we are looking after value of cell B3.
- The formula in cell F2 would be
- Press enter on your keyboard.
- The function will return the value which is in 3rd row and 2nd column.