Pulling Values With INDEX

The INDEX function answers a simple question: "What value lives at a specific position in a range?" You give it a block of cells and a row number, and it hands back whatever sits there.

Think of a range like a numbered list. INDEX is like saying "give me item number 3." It does not search for anything by name yet, it just grabs by position.

This makes INDEX the perfect partner for retrieving values once you know where they are.

The simplest form of INDEX takes two pieces of information:

• array — the range of cells to look in
• row_num — which row of that range to return

So =INDEX(B2:B6, 3) reads: "From the range B2:B6, return the value in the 3rd position." If B2:B6 holds names, this returns the 3rd name.

The row number is counted inside the range, not from the top of the sheet.

This trips people up at first. If your range starts at row 2, then the cell in row 2 is position 1, not 2.

Imagine B2:B6 holds: Apple, Banana, Cherry, Date, Fig. Position 1 is Apple (in cell B2), position 2 is Banana, and position 3 is Cherry.

So =INDEX(B2:B6, 3) returns Cherry. The position is always relative to the start of the range you gave it.

Suppose column A lists employee names and you want the 4th one. Your data sits in A2:A10.

You write =INDEX(A2:A10, 4). INDEX counts down four positions from A2 and returns the name in A5.

Notice you never typed a name. INDEX does not care what the value is, only where it is. That positional nature is exactly why it pairs so well with functions that find positions for you.

INDEX also has a fuller, two-dimensional form for when your range spans several columns:

=INDEX(array, row_num, column_num)

Now you specify both a row and a column inside the block. For example =INDEX(A2:C10, 4, 2) returns the value at the 4th row, 2nd column of that block.

Both numbers count from the top-left corner of the range you supplied.

Imagine a small table in A2:C5 where column A is Region, column B is Product, and column C is Sales. You want the Sales figure from the 3rd data row.

Sales is the 3rd column, and the 3rd row of data is position 3. So you write =INDEX(A2:C5, 3, 3).

INDEX jumps to row 3, column 3 of that block and returns that single sales value. One formula, exact intersection.

If you set one of the numbers to 0 (or leave it out in many cases), INDEX returns an entire row or column instead of a single cell.

=INDEX(A2:C5, 0, 2) returns the whole 2nd column of the block. In modern spreadsheets this spills down into multiple cells.

This is handy when you need to feed an entire column into another function rather than just one value.

When your range is just one column wide, you can skip the column argument entirely. INDEX assumes column 1.

So for prices in C2:C20, =INDEX(C2:C20, 7) simply returns the 7th price. No column number needed.

Likewise, for a single row of values like E1:J1, =INDEX(E1:J1, 1, 4) returns the 4th value across that row. INDEX flexes to match the shape of your data.

You might wonder why not just type =A5 directly. The power of INDEX is that the position can be a calculation or a cell reference, not a fixed number.

For example =INDEX(A2:A100, D1) returns whichever row number is stored in D1. Change D1 to 5 and INDEX returns the 5th item; change it to 20 and it returns the 20th.

This is what turns INDEX from a curiosity into a dynamic lookup tool.

Say you have a leaderboard in A2:A11 and a dropdown in F1 where a user picks a rank number from 1 to 10.

You write =INDEX(A2:A11, F1). When someone selects 1 in F1, they see the top player; selecting 3 shows the third-place player.

The formula never changes, only the input does. This is the seed of every flexible lookup you will build next.

If you ask INDEX for a position that does not exist, you get a #REF! error. For a range of 5 items, asking for position 6 fails.

For example =INDEX(A2:A6, 9) returns #REF! because there is no 9th item in a 5-row range.

Keep your position number within the size of the range. When you later feed positions from MATCH, this rarely happens, but it is good to recognize the error.

Test your understanding of how INDEX counts positions.

You learned that INDEX retrieves a value by its position inside a range:

• =INDEX(array, row_num) for a single column or row
• =INDEX(array, row_num, column_num) for a two-dimensional block
• Positions count from the start of the range, so the first cell is position 1
• The position can be a number, a cell reference, or a calculation, which makes INDEX dynamic

Next you will learn MATCH, the function that finds those positions for you automatically.