How Does The INDEX Function Work in Excel? | Step By Step

If INDEX feels abstract at first, think of it as a pointer. You give Excel a block of cells, then tell it which row and column inside that block to use. Excel goes straight to that spot and returns what sits there.

That makes INDEX handy for price lists, grade books, staff rosters, and inventory sheets. It also holds up well when a worksheet changes, since it doesn’t rely on a hard-coded column number the way older lookup formulas often do.

How The Excel INDEX Function Works With Row And Column Numbers

The basic pattern is straightforward: =INDEX(array, row_num, [column_num]). The first part is the range you want to search inside. The row number tells Excel how far down to move within that range. The column number tells Excel how far across to move.

Take a range like B2:D6. Inside that range, B2 is row 1, column 1. D6 is row 5, column 3. So if you write =INDEX(B2:D6,3,2), Excel returns the value from the third row and second column inside that selected block, not the third row of the full worksheet.

That “inside the selected block” part trips people up. INDEX counts from the top-left cell of the range you gave it. Once that clicks, the function starts to feel a lot less mysterious.

What INDEX Returns

Most of the time, INDEX returns a single value. If the intersecting cell holds 89, you get 89. If it holds “Bananas,” you get that text. Microsoft’s INDEX function documentation also notes that INDEX has two forms: an array form and a reference form.

The array form is the one most people use. The reference form is for cases where you want INDEX to return a reference from one or more ranges instead of pulling a single visible value right away. That sounds niche, and it is, though it can help in larger models.

Why INDEX Feels Better Than Basic Lookups In Many Sheets

INDEX shines when your return column is not fixed forever. With a formula like VLOOKUP, shifting columns can break the setup or force a rewrite. With INDEX, you point straight at the return range you want, then feed it the right position. That makes the formula easier to trust when a sheet grows over time.

It also works well for two-way lookups. You can use one value to find the row and another to find the column, then let INDEX return the value where those two meet. That setup is a natural fit for score grids, price matrices, and monthly reports.

INDEX And MATCH Work As A Pair

On its own, INDEX needs position numbers. MATCH supplies them. The MATCH function page explains that MATCH returns the relative position of an item in a range. Put the two together, and you can find a value by label instead of by hard-coded row numbers.

Say product names are in A2:A10 and prices are in B2:B10. This formula returns the price for “Mango”:

=INDEX(B2:B10,MATCH("Mango",A2:A10,0))

Here’s the flow. MATCH searches A2:A10 for “Mango” and returns its position. If Mango is the fourth item in that list, MATCH returns 4. INDEX then goes to the fourth item in B2:B10 and returns that price.

That exact-match zero matters. Without it, Excel may use match rules you didn’t mean to use. Microsoft also shows how INDEX and MATCH can replace VLOOKUP when the lookup value is not in the leftmost column in its lookup values with VLOOKUP, INDEX, or MATCH article.

INDEX Arguments And Behaviors At A Glance

What INDEX Can Do Beyond A Basic Pull

Once you get the core idea, INDEX starts to open up. You can use it to fetch one item, a full row, a full column, or an intersecting value inside a matrix. In newer Excel versions, a formula with a zero for row or column can spill a whole set of values into nearby cells.

You can also build a two-way lookup with one MATCH for rows and another MATCH for columns. That pattern is clean and easy to audit:

=INDEX(B2:F10,MATCH(H2,A2:A10,0),MATCH(H3,B1:F1,0))

In that formula, H2 might hold a product name and H3 might hold a month. INDEX returns the value where that product row meets that month column. That’s one of the nicest uses of INDEX in real workbooks.

When The Reference Form Matters

The reference form uses =INDEX(reference,row_num,[column_num],[area_num]). It comes up when you want Excel to choose from more than one range. You can think of area_num as the selector that tells INDEX which block to use first, then which row and column to read inside that block.

Many users never need this. Still, knowing it exists helps when you inherit a workbook built by someone who used several named ranges and packed a lot into one formula.

Common INDEX Formulas You’ll Actually Use

Common Mistakes That Make INDEX Feel Broken

The most common issue is using worksheet row numbers instead of range row numbers. If your range starts at B2, then B2 is row 1 to INDEX, not row 2. That off-by-one slip can throw the whole result off.

Another problem is mixing ranges of different sizes in an INDEX and MATCH formula. If your lookup list runs from A2:A10 but your return range runs from B2:B8, the position numbers no longer line up. Keep both ranges aligned.

Approximate matching can also cause confusion. When you want one exact item, use 0 in MATCH. If you leave that argument out, Excel may return the nearest position based on sorted data rules instead of the exact item you expected.

Then there’s the error side. #REF! points to a row or column request outside the selected range. #N/A often means MATCH did not find what you asked for. If a workbook is older, an array-style setup may also need special entry rules tied to the Excel version in use.

When To Reach For INDEX Or MATCH

INDEX still earns its place. It is flexible, stable, and easy to scale once you know the logic. MATCH remains a solid partner when you need an exact position from a row or column.

That pairing is still one of the clearest ways to build a lookup that can search by label, return data from any direction, and stay readable when the sheet changes.

The Main Idea

INDEX works by reading a chosen range and returning the value at a row and column position inside that range. That’s the full idea. Start with a small block of cells, test a few row and column numbers, then pair INDEX with MATCH when you want the sheet to find positions for you.

After a few tries, INDEX stops feeling like a strange lookup formula and starts feeling like one of the cleanest tools in Excel.

Send us your feedbackTell us what you thought about this post

⚠Please use a real email you check. If it's fake or mistyped, your message won't reach us and we can't reply — wrong addresses are rejected automatically.

Name *Email *

Your comment *

Quick check — what is …? *

Send feedback