INDEX function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the value from the 2nd row and 3rd column of the range A1:D5.

Excel

=INDEX(A1:D5, [1], 3)

Drag options to blanks, or click blank then click option'

A3

B2

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using the column number instead of the row number in the second argument.

Confusing row and column positions.

The INDEX function returns the value at the intersection of the specified row and column. Here, the row is 2 and the column is 3.

2fill in blank

medium

Complete the formula to get the value from the 4th row and 1st column of the range B2:E6.

Excel

=INDEX(B2:E6, [1], 1)

Drag options to blanks, or click blank then click option'

A3

B2

C4

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using the absolute row number from the sheet instead of relative to the range.

Mixing up row and column numbers.

The INDEX function uses the row number as the second argument. Here, 4 means the 4th row in the range.

3fill in blank

hard

Fix the error in the formula to correctly return the value from the 3rd row and 2nd column of range C1:F4.

Excel

=INDEX(C1:F4, [1], 2)

Drag options to blanks, or click blank then click option'

A4

B1

C2

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using a row number outside the range's size.

Confusing row and column arguments.

The row argument should be 3 to get the 3rd row in the range. Using 4 or 2 would give wrong or error results.

4fill in blank

hard

Fill both blanks to get the value from the 1st row and 4th column of the range D3:G7.

Excel

=INDEX(D3:G7, [1], [2])

Drag options to blanks, or click blank then click option'

A1

B2

C4

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using sheet row or column numbers instead of relative to the range.

Swapping row and column numbers.

The first blank is the row number (1), and the second blank is the column number (4) within the range.

5fill in blank

hard

Fill all three blanks to get the value from the 2nd row and 3rd column of the range A2:D6 using INDEX with the optional area_num argument set to 1.

Excel

=INDEX((A2:D6, E2:H6), [1], [2], [3])

Drag options to blanks, or click blank then click option'

A2

B3

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to specify the area number when using multiple ranges.

Mixing up row and column numbers.

The first blank is the row number (2), the second is the column number (3), and the third is the area number (1) to select the first 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