INDEX function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INDEX formula?

Given the table below in Excel cells A1:B4:

A1: Name  B1: Score
A2: Anna  B2: 85
A3: Ben   B3: 90
A4: Cara  B4: 78

What is the result of the formula =INDEX(B2:B4, 2)?

A 90

B 85

C 78

D #REF!

💡 Hint

Remember, INDEX returns the value at the given position in the range.

The range B2:B4 contains scores 85, 90, and 78. The formula asks for the 2nd item, which is 90.

❓ query_result

intermediate

What does this INDEX formula return with row and column numbers?

Given the table in cells A1:C3:

A1: Product  B1: Price  C1: Stock
A2: Pen      B2: 1.5    C2: 100
A3: Book     B3: 12     C3: 50

What is the result of =INDEX(A1:C3, 3, 2)?

A 12

B 50

C Book

D Stock

💡 Hint

INDEX with two numbers returns the value at the intersection of that row and column.

Row 3, column 2 in the range A1:C3 is cell B3, which contains 12.

🔧 Formula Fix

advanced

Which INDEX formula is syntactically correct?

Choose the correct syntax for the INDEX function to return the 4th item from range D1:D10.

A =INDEX(D1:D10,)

B =INDEX(D1:D10; 4)

C =INDEX(D1:D10, 4)

D =INDEX(D1:D10 4)

💡 Hint

Check the correct use of commas and parentheses in Excel formulas.

Option C uses the correct syntax with a comma separating arguments and no missing parts.

❓ optimization

advanced

Which INDEX formula efficiently returns the last value in a column?

Given a column of numbers in A1:A100, which formula returns the last non-empty value efficiently?

A =INDEX(A1:A100, 100)

B =INDEX(A1:A100, COUNTA(A1:A100))

C =INDEX(A1:A100, MATCH(9.99999999999999E+307, A1:A100))

D =INDEX(A1:A100, ROWS(A1:A100))

💡 Hint

Think about how to count non-empty cells to find the last value.

COUNTA counts non-empty cells, so INDEX with COUNTA returns the last filled cell's value.

🧠 Conceptual

expert

What error occurs with this INDEX formula and why?

Consider the formula =INDEX(B2:B5, 0). What error does it produce and why?

A #VALUE! error because row number cannot be zero

B Returns the entire range B2:B5

C Returns the first item in B2:B5

D #REF! error because zero is out of range

💡 Hint

Check the valid row numbers for INDEX function.

Row number must be a positive integer; zero is invalid and causes #REF! error.

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