Excel + Google Sheets · INDEX
Get the value at any position
INDEX returns the value found at a given position of a list or table.
INDEX answers a simple question: what is in position 3 of this list? You give it a list and a number, and it returns the value found there. It looks small, but it is one of the most useful functions in Excel because it never depends on the order or the width of your data.
INDEX works in every version of Excel, in Excel 365 and in Google Sheets, so you can share your file with anyone. On its own it is handy; combined with MATCH it becomes the classic lookup that works in any Excel. Read the MATCH guide next to see how the two fit together, or compare it with the XLOOKUP guide.
How it works INDEX
INDEX takes the range to read and the position you want. Write the formula like this:
=INDEX(A2:A5,C2)Here Excel reads the client list in A2:A5 and returns the item in the position written in cell C2. If C2 holds 3, you get the third client. The position counts from the first cell of the range, not from the sheet row.
Example step by step
- Type four client names in A2:A5.
- Type the position you want in C2, for example 3.
- Click D2, type
=INDEX(A2:A5,C2)and press Enter. - Change the number in C2 and watch the result change.
| Client | Row | Result |
|---|---|---|
| Laura | 3 | Priya |
| João | ||
| Priya | ||
| Tom |
The result is Priya, the third name in the list. Change C2 to 1 and you get Laura. If you type a position bigger than the list, Excel shows the error #REF!, which you can catch with IFERROR.
Common errors
- The position is larger than the range and the result is #REF!. Check the size of the range or wrap the formula in IFERROR.
- The position counts from the sheet row instead of the range. In A2:A5 the first item is position 1, not 2.
- The position is typed as text, such as 3 with a space. Convert it to a number.
- The range changes size when you insert rows and the position points to the wrong item. Use a whole table or a named range.
Used well, INDEX makes your reports flexible: the position can come from another formula, a drop-down list or a MATCH. Compare the first result with a manual count to be sure you are reading the right cell. INDEX works in Excel 2010 or later, Excel 365 and Google Sheets.