INDEX function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INDEX function do in Excel?

The INDEX function returns the value of a cell at the intersection of a specified row and column within a range or array.

beginner

Syntax of the INDEX function?

The syntax is INDEX(array, row_num, [column_num]).
- array: range or array to look in
- row_num: row number to find
- column_num: optional column number to find

beginner

What happens if you omit column_num in INDEX?

If column_num is omitted, INDEX returns the value from the specified row in the first column of the array.

intermediate

How can INDEX help when combined with MATCH?

INDEX can return a value from a table using a row and column number found by MATCH, enabling dynamic lookups.

beginner

Example: What does =INDEX(A1:C3, 2, 3) return?

It returns the value in the 2nd row and 3rd column of the range A1:C3.

What does INDEX(A1:B4, 3, 2) return?

A Value in 2nd row, 3rd column of A1:B4

B Value in 3rd row, 2nd column of A1:B4

C Value in 3rd row, 1st column of A1:B4

D Value in 1st row, 2nd column of A1:B4

INDEX returns the value at the specified row and column in the range.

If column_num is omitted in INDEX, which column is used?

A First column

B Middle column

C Last column

D No column, error

Omitting column_num defaults to the first column.

Which function is commonly paired with INDEX for dynamic lookups?

A MATCH

B SUM

C IF

D VLOOKUP

MATCH finds the position, INDEX returns the value.

What type of value does INDEX return?

A Cell reference

B Error code

C Formula

D Value from a cell

INDEX returns the actual value stored in the cell.

What will =INDEX(A1:C3, 1, 1) return?

A Error

B Value in cell C3

C Value in cell A1

D Value in cell B2

Row 1, column 1 corresponds to cell A1.

Explain how the INDEX function works and its main parameters.

Think about how you find a seat in a theater by row and seat number.

Describe a practical example where combining INDEX and MATCH is useful.

Imagine looking up a friend's phone number in a contact list by name.

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