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.