INDEX function in Excel - Cell-by-Cell Formula Trace

Sample Data

A simple product list with their prices in two columns: Product names in column A and prices in column B.

Cell	Value
A1	Product
B1	Price
A2	Apple
B2	1.20
A3	Banana
B3	0.50
A4	Cherry
B4	2.00
A5	Date
B5	3.00

Formula Trace

=INDEX(B2:B5, 3)

Step 1: B2:B5

Step 2: INDEX([1.20, 0.50, 2.00, 3.00], 3)

Cell Reference Map

    A       B
1 Product  Price
2 Apple    1.20
3 Banana   0.50
4 Cherry   2.00  <-- INDEX range
5 Date     3.00

The formula references the price range B2:B5 to find the 3rd item.

Result

    A       B
1 Product  Price
2 Apple    1.20
3 Banana   0.50
4 Cherry   2.00
5 Date     3.00

Result in formula cell: 2.00

The formula returns 2.00, the price of the 3rd product (Cherry) in the range B2:B5.

Sheet Trace Quiz - 3 Questions

What does the formula =INDEX(B2:B5, 3) return?

A 2.00

B 0.50

C 3.00

D 1.20

Key Result

INDEX(range, row_number) returns the value at the given row position within the range.

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