In Excel: use =INDEX(range, row_number, [column_number]) — it returns the value sitting at that position inside the range, counting from the range's own first cell.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
array | required | The range to return a value from. |
row_num | required | Which row of the array to return, counted from its first row. 0 returns the whole column. |
column_num | optional | Which column of the array to return. Required when the array spans more than one column. |
Related functions
Type =INDEX( and select the range holding the values you want back.
Give the row number — counted from the first row of that range, not from row 1 of the sheet.
Add a column number if the range is more than one column wide.
Replace a hard-coded position with MATCH to make it a lookup: =INDEX(B2:B100, MATCH(E2, A2:A100, 0)).
Anchor the ranges with $ before filling the formula down.
For a whole row or column as an array, pass 0 for the coordinate you want to keep open.
What this does
INDEX is the opposite of MATCH: you give it a position and it hands back the value. On a one-column range, =INDEX(B2:B100, 7) returns the seventh cell. On a two-dimensional range you supply both coordinates, =INDEX(B2:E100, 7, 3), which returns row 7, column 3 of that block. Two behaviours make it more than a convenience. Passing 0 as row_num or column_num returns the entire column or row as an array, which is how you feed a whole column into SUM or a chart. And INDEX returns a reference, not just a value, so =SUM(B2:INDEX(B2:B100, 20)) is a legal, dynamically-ending range. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “use excel index function”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Products in A2:A100, prices in B2:B100. =INDEX(B2:B100, 7) returns the price on the seventh row of the list — 24.90. Paired with MATCH to look it up by name: =INDEX(B2:B100, MATCH("Widget", A2:A100, 0)) returns the Widget price regardless of where it sits, and unlike VLOOKUP it works when the name column is to the RIGHT of the price. Two-dimensional: with months across C1:H1 and products down A2:A50, =INDEX(C2:H50, MATCH("Widget", A2:A50, 0), MATCH("Mar", C1:H1, 0)) returns the Widget figure for March. INDEX is the return half of the most flexible lookup pattern in Excel: it fetches from any column in any direction, survives column inserts, and can hand back an entire row or column when you need an array rather than a single value. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. The short version of “use excel index function”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Counting the position from the sheet's row 1 instead of from the range's first row — a range starting at
B2makesB2position 1. - Omitting column_num on a multi-column array, which returns
#VALUE!or the wrong column. - Selecting an array that includes the header row, which shifts every position by one.
- Hard-coding the row number, which breaks the moment a row is inserted; pair it with
MATCHinstead. - Using
INDEX/MATCHout of habit in Microsoft 365 whereXLOOKUPis shorter and easier for someone else to read.
Frequently asked questions
What does the INDEX function do?
It returns the value at a given position inside a range: =INDEX(B2:B100, 7) is the seventh cell of that range. With a two-dimensional range you pass both a row and a column number.
What is the difference between INDEX and VLOOKUP?
VLOOKUP searches the leftmost column and returns a value to its right. INDEX takes a position and returns the value there — so paired with MATCH it can look leftwards, and it does not break when a column is inserted.
Why does INDEX return the wrong row?
The position is counted from the first cell of the array you passed, not from the top of the sheet. If your array starts at B2, then row_num 1 is B2. Including the header row in the array shifts everything by one.
Can INDEX return a whole column?
Yes — pass 0 as row_num: =SUM(INDEX(B2:E100, 0, 3)) sums the third column of the range. Passing 0 as column_num returns a whole row the same way.
Other ways people ask this
People reach this page typing “index function excel”, “excel function index”, “how to use the index function in excel” and “using the index function in excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “index function excel”, “excel function index”, “how to use the index function in excel” all point at the one operation explained on this page, which is why they all lead here.