Retrieving Values Using References Based on Row and Column Numbers in Microsoft Excel 2010

 

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.

Syntax: =INDIRECT(ref_text,A1)
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

img1

ADDRESS: Creates a cell reference as text, given specified row & column numbers

Syntax: =ADDRESS(row_num,column_num,abs_num,A1,sheet_text)

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.

img2

  • 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
  • =INDIRECT(ADDRESS(D2,E2))
  • Press enter on your keyboard.
  • The function will return the value which is in 3rd row and 2nd column.

img3



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>