Retrieving the Nth Value in a Row, Column and Range in Microsoft Excel 2010

In this article, we will learn how to retrieve the Nth value in a Row, Column, and range. We use a combination of “OFFSET”, “Index”, and “ROW” functions in Microsoft Excel 2010.

OFFSET: -One of the rows and columns in a range that is a specified number of the cells or range of cells has a reference returned references. A single cell or range of cells, you may have to be returned to the number of rows and number of columns, can be specified.

The syntax of OFFSET function:    =OFFSET (reference, rows, cols, height, width)

Reference:- This is a cell or range from which you want to offset.

Rows and Columns to move: – How many rows you want to move from the starting point, and both of these can be positive, negative or zero.

Height and Width: – This is the size of the range you want to return.

Let’s take an example to understand how the Offset function works in excel.

We have data in range A1:D10 in which column A contains Product Code, Column B contains Quantity, column C contains per product cost and column D contains Total cost. We want to return the value of cell C5 in cell E2.
 
img1
 
Follow below mentioned steps.

  • Select the cell E2 and write the formula.
  • =OFFSET(A1,4,2,1,1)
  • Press Enter on your keyboard.
  • The function will return the value of cell C5.

 
img2
 
img3
 
ROW: – This function is used to return the row number of a cell reference.

Syntax of “ROW” function:           =ROW (reference)

Example: =ROW (A1) would return 1

               =ROW (A1:A10) would return 1

              =ROW (A1:C10) would return 1  

 
img4
 
img5
 
img6
 
INDEX: The Index formula returns a value from the intersection between the row number and the column number in an Array. There are 2 syntaxes for the “INDEX” function.

 

1st Syntax of “INDEX” function:=INDEX (array, row_num, [column_num])

2nd Syntax of “INDEX” function:=INDEX (reference, row_num, [column_num], [area_num])

For example, we have data in range A1:C5 in which column A contains Order Id, column B contains Unit Price, and column C contains Quantity.

Follow below given steps:-

  • Write the formula in cell D2.
  • =INDEX(A1:C5,2,3)
  • Press Enter on your keyboard.
  • The function will return 10, which means 10 is available in 2nd row and 3rd column.

 
img7
 
Let’s take an example to understand how we can retrieve the Nth value in a row, column and range.

We have data in range A1:B12.
 
img8
 
To retrieve the Nth value in the column, follow below given steps:-

  • Write the formula in cell C2.
  • =OFFSET($B$1,0,C4-1)
  • Press Enter on your keyboard.
  • The function will return the Nth value in the column.

 
img9
 
To retrieve the Nth value in the Row, follow below given steps:-

  • Write the formula in cell C2.
  • =OFFSET($A$4,C4-1,0)
  • Press Enter on your keyboard.
  • The function will return the Nth value in the row.

 
img10
 
To retrieve the Nth value in the Range, follow below given steps:-

  • Write the formula in cell C2.
  • =INDEX($A$2:$A$12,(ROW()-2)*3+3)
  • Press Enter on your keyboard.
  • The function will return the Nth value in the range.

 
img11
 
This is the way we can retrieve the Nth value in a row, column, and range in Microsoft Excel 2010.
 
 

Users are saying about us...

  1. Column 1& 2 Unique value can be manually entered. I want the numbers laid by the excel function from the list wrt each row and column as display

    Column 1 Column 2 Column 3
    A W 1
    A X 2
    A Y 3
    A Z 4
    B W 5
    B X 6
    B Y 7
    B Z 8
    C W 9
    C X 10
    C Y 11
    C Z 12
    D W 13
    D X 14
    D Y 15
    D Z 16

    W X Y Z
    A 1 2 3 4
    B 5 6 7 8
    C 9 10 11 12
    D 13 14 15 16

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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube