INDEX function in Excel - Step-by-Step Guide

Introduction

The INDEX function helps you find a value inside a table or range by giving its row and column numbers. It is useful when you want to get data from a specific position without searching manually.

When you want to get the price of a product from a price list by its row and column position.

When you have a table of monthly sales and want to find the sales number for a specific month and product.

When you want to extract a value from a large data set without scrolling or filtering.

When you combine it with other functions like MATCH to find data dynamically.

When you want to avoid errors from manually looking up values in a table.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the formula

💡 Start with =INDEX(

Step 3: Select

- the range of cells containing your data

The range appears inside the formula

💡 For example, select A1:C5

Step 4: Type

- the formula bar after the range

You add the row number

💡 For example, type ,2 to get the second row

Step 5: Type

- the formula bar after the row number

You add the column number

💡 For example, type ,3 to get the third column

Step 6: Type

- the formula bar

The formula looks like =INDEX(A1:C5,2,3)

💡 Press Enter to see the result

Step 7: Press

- Enter key

The cell shows the value from row 2, column 3 of the selected range

Before vs After

Before

Cell is empty, no formula entered

After

Cell shows the value from the specified row and column in the selected range, for example 150

Settings Reference

Array argument

📍 First argument in the INDEX formula

Defines the table or range where to look for the value

Default: No default, must specify

Row number argument

📍 Second argument in the INDEX formula

Specifies which row to pick from the array

Default: No default, must specify

Column number argument

📍 Third argument in the INDEX formula

Specifies which column to pick from the array

Default: 1 if omitted

Common Mistakes

Entering a row or column number that is outside the range size

Excel returns a #REF! error because the position does not exist

Check the size of your range and use row and column numbers within that size

Forgetting to separate arguments with commas

Excel shows a formula error because syntax is incorrect

Always separate the array, row number, and column number with commas like =INDEX(A1:C5,2,3)

Using INDEX without specifying the row number

Excel returns an error because row number is required

Always provide at least the row number argument

Summary

The INDEX function returns a value from a table by row and column numbers.

You must specify the range, row number, and optionally the column number.

It helps you find data quickly without searching manually.

Practice

1. What does the Excel INDEX function do?

easy

A. Returns a value from a range based on row and column numbers

B. Sorts a list of values alphabetically

C. Counts the number of cells with numbers

D. Finds the position of a value in a list

Solution

1. Step 1: Understand the purpose of INDEX

   The INDEX function returns a value from a specified position in a range, using row and optionally column numbers.

2. Step 2: Compare with other options

   Sorting, counting, and finding positions are done by other functions like SORT, COUNT, and MATCH, not INDEX.

3. Final Answer:

   Returns a value from a range based on row and column numbers -> Option A

4. Quick Check:

   INDEX = value by position [OK]

Hint: INDEX returns value by position, not by sorting or counting [OK]

Common Mistakes:

• Confusing INDEX with MATCH which finds position
• Thinking INDEX sorts data
• Assuming INDEX counts cells

2. Which of the following is the correct syntax for the INDEX function in Excel?

easy

A. INDEX(array; column_num; row_num)

B. INDEX(row_num, array, [column_num])

C. INDEX(array, row_num, [column_num])

D. INDEX(array, column_num, row_num)

Solution

1. Step 1: Recall the correct parameter order

   The correct syntax is INDEX(array, row_num, [column_num]) where array is the range, row_num is the row number, and column_num is optional.

2. Step 2: Check other options for errors

   Options B, C, and D have wrong parameter order or use wrong separators (semicolon instead of comma).

3. Final Answer:

   INDEX(array, row_num, [column_num]) -> Option C

4. Quick Check:

   Syntax = INDEX(array, row, [col]) [OK]

Hint: Remember: array first, then row, then optional column [OK]

Common Mistakes:

• Swapping row and column numbers
• Using semicolons instead of commas
• Putting row number before array

3. Given the range A1:C3 with values:
1 2 3
4 5 6
7 8 9
What is the result of =INDEX(A1:C3, 2, 3)?

medium

A. 8

B. 6

C. 5

D. 3

Solution

1. Step 1: Locate the cell at row 2, column 3 in the range

   Row 2 is the second row: 4, 5, 6. Column 3 is the third value in that row, which is 6.

2. Step 2: Confirm the value returned by INDEX

   INDEX returns the value at that position, which is 6.

3. Final Answer:

   6 -> Option B

4. Quick Check:

   Row 2, Col 3 = 6 [OK]

Hint: Row then column picks the value in that cell [OK]

Common Mistakes:

• Mixing up row and column numbers
• Picking value from wrong row or column
• Confusing INDEX with MATCH

4. What is wrong with this formula: =INDEX(A1:B2, 3, 1) if the range is only 2 rows tall?

medium

A. Column number 1 is invalid

B. INDEX requires a column number for 2D ranges

C. Range A1:B2 is not a valid range

D. Row number 3 is outside the range, causing an error

Solution

1. Step 1: Check the size of the range

   The range A1:B2 has 2 rows and 2 columns only.

2. Step 2: Validate the row number parameter

   The formula uses row number 3, which is outside the 2-row range, causing a #REF! error.

3. Final Answer:

   Row number 3 is outside the range, causing an error -> Option D

4. Quick Check:

   Row 3 > 2 rows = error [OK]

Hint: Row or column number must be inside range size [OK]

Common Mistakes:

• Using row or column numbers larger than range
• Assuming INDEX auto-corrects out-of-range numbers
• Ignoring #REF! errors

5. You have a table in A1:C4 where column A is names, column B is ages, and column C is cities:
John 25 NY
Mary 30 LA
Steve 22 SF
Anna 28 CHI
How would you use INDEX to find the city of the person in row 3?

hard

A. =INDEX(A1:C4, 3, 3)

B. =INDEX(A1:C4, 3, 2)

C. =INDEX(B1:B4, 3)

D. =INDEX(A1:A4, 3)

Solution

1. Step 1: Identify the correct range and position

   The full table is A1:C4. Row 3 corresponds to Steve's data. The city is in column 3.

2. Step 2: Use INDEX with full range, row 3, column 3

   INDEX(A1:C4, 3, 3) returns the value in row 3, column 3, which is Steve's city "SF".

3. Final Answer:

   =INDEX(A1:C4, 3, 3) -> Option A

4. Quick Check:

   Row 3, Col 3 = city [OK]

Hint: Use full table range with row and column for exact cell [OK]

Common Mistakes:

• Using wrong column number for city
• Using only one column range losing row context
• Confusing age column with city column