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

Posted by
Sokhom
Labels:
Functions

**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

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

- 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

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

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

Subscribe to:
Post Comments (Atom)

Post a Comment