How to use the INDEX and OFFSET function to find data in a table in Excel - Excel Training & Excel Tutorial

Latest

Excel Training, Excel Tutorial that could you become Excel Expert

BANNER 728X90

Friday, February 18, 2011

How to use the INDEX and OFFSET function to find data in a table in Excel

INDEX Function

There are two forms of the INDEX function, Array and Reference. The primary differences between the two forms are as follows:
  • The Array form can return more than one value at a time. The Reference form returns the reference of the cell at the intersection of a particular row and column.
  • The Array form is entered using CTRL + SHIFT + ENTER, instead of just ENTER, as with Reference.
Enter the following data into a blank Excel worksheet. You will use this data for the sample formula in this article.

A
B
C
1
Name
Dept
Age
2
Henry
501
28
3
Stan
201
19
4
Mary
101
22
5
Larry
301
29
6
7
Harry
401
21
8
Joe
101
23
9
Lynn
301
30

Reference Form of INDEX

  1. Enter the following formula into cell E2 (or any available blank cell):
=INDEX((A2:C5,A7:C9),2,3,2)
(A2:C5,A7:C9) are the ranges where the value that you want will be found.

2 is the row number in the range where the value is.

3 is the column number in the ranges where the value is. Because there are three columns (beginning with column A), the third column is column C.

2 is the area, A2:C5 or A7:C9, where the value is. Because there are two areas specified for the range, the second range is A7:C9.
  1. Press ENTER.
In the sample formula, the INDEX function returns a value at the intersection of the second row (2) and third column (C) of the range A7:C9. The value in cell C8 is 23. Therefore, the formula =INDEX((A2:C5,A7:C9),2,3,2) will return the value 23.

Array Form of INDEX

  1. Enter the following formula into cell E3 (or any available blank cell):
=INDEX(A2:C5,2,3)
A2:C5 is the range where the value that you want will be found.

2 is the row number in the range where the value is.

3 is the column number in the range where the value is. Because there are three columns (beginning with column A), the third column is column C.
  1. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.
In the sample formula, the INDEX function returns a value at the intersection of the second row (2) and third column (C). The value in cell C2 is 19. Therefore, the formula =INDEX(A2:C5,2,3) will return the value 19.

OFFSET Function

Enter the following data into a blank Excel worksheet. You will use this data for all sample formulas in this article.

A1: Name
B1: Dept
C1: Age
A2: Henry
B2: 501
C2: 28
A3: Stan
B3: 201
C3: 19
A4: Mary
B4: 101
C4: 22
A5: Larry
B5: 301
C5: 29

Enter the following formulas into cell E2 (or any available blank cell):
=OFFSET(C2,2,-1,1,1)
  • C2 - The referenced cell.
  • 2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
  • -1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
  • 1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
  • 1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

Examples:

When you use this formula, the OFFSET function returns the value of the cell that is located two rows down (2) and 1 row to the left (-1) of cell C2 (which is cell B4). The value in cell B4 is "101". Therefore, the formula returns "101".

No comments:

Post a Comment