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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “use the index formula in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. 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 turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. Treat “use the index formula in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
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
This is also commonly searched as “index formula in excel”, “excel formula index”, “how to use index excel” and “how to use index formula in excel”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “index formula in excel”, “excel formula index”, “how to use index excel” all point at the one operation explained on this page, which is why they all lead here.