Subscribe Us

Enter your email address:

Loading

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

Labels:

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".

'



0 comments:

Post a Comment

Post a Comment