INDEX and MATCH combination in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the INDEX function do in Google Sheets?

The INDEX function returns the value of a cell at a specific row and column in a given range.

beginner

What is the purpose of the MATCH function in Google Sheets?

MATCH finds the position of a value in a range, returning the relative row or column number.

intermediate

How do INDEX and MATCH work together?

MATCH finds the position of a value, and INDEX uses that position to return the actual value from another range.

intermediate

Write a simple formula using INDEX and MATCH to find a price in a product list.

Example: =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))
This finds 'Apple' in A2:A10 and returns the price from B2:B10 at the same row.

advanced

Why is INDEX and MATCH combination better than VLOOKUP?

INDEX and MATCH can look left or right, is faster on large data, and doesn’t break if columns are added or removed.

What does MATCH return in Google Sheets?

A The sum of values in the range

B The value found in the range

C The position of the value in the range

D The average of values in the range

MATCH returns the position (row or column number) of the searched value within the range.

Which function returns a value from a specific position in a range?

A INDEX

B SUM

C VLOOKUP

D MATCH

INDEX returns the value at a given row and column in a range.

In the formula =INDEX(B2:B10, MATCH("Apple", A2:A10, 0)), what does the 0 in MATCH mean?

A Finds the closest match

B Finds an exact match

C Ignores case sensitivity

D Searches from bottom to top

The 0 tells MATCH to find an exact match.

Why might you choose INDEX and MATCH over VLOOKUP?

A Because INDEX and MATCH are easier to write

B Because VLOOKUP is slower

C Because VLOOKUP can only sum values

D Because INDEX and MATCH can look left and right

INDEX and MATCH can look up values to the left or right, unlike VLOOKUP which only looks right.

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

A Value in row 2, column 3

B Value in row 1, column 3

C Value in row 3, column 2

D Value in row 3, column 1

INDEX returns the value at row 2, column 3 of the range A1:C3.

Explain how you would use INDEX and MATCH together to find a value in a table.

Think about how MATCH locates the row and INDEX retrieves the data.

Describe one advantage of using INDEX and MATCH over VLOOKUP.

Consider flexibility and reliability when table structure changes.

Practice

1. What does the INDEX function do in Google Sheets when combined with MATCH?

easy

A. Deletes duplicate values from a range

B. Finds the position of a value in a range

C. Sorts the data in ascending order

D. Returns the value at a specific position in a range

Solution

1. Step 1: Understand the role of INDEX

   The INDEX function returns the value located at a specific row and column in a given range.

2. Step 2: Understand the role of MATCH

   The MATCH function finds the position (row or column number) of a specific value within a range.

3. Final Answer:

   Returns the value at a specific position in a range -> Option D

4. Quick Check:

   INDEX returns value, MATCH finds position [OK]

Hint: INDEX gives value; MATCH gives position [OK]

Common Mistakes:

• Confusing INDEX with MATCH roles
• Thinking INDEX sorts data
• Assuming MATCH returns values

2. Which of the following is the correct syntax to use INDEX and MATCH together to find a value in column B based on a lookup value in column A?

easy

A. =INDEX(A1:A10, MATCH(B1:B10, "Apple", 0))

B. =MATCH(B1:B10, INDEX("Apple", A1:A10, 0))

C. =INDEX(B1:B10, MATCH("Apple", A1:A10, 0))

D. =MATCH("Apple", B1:B10, INDEX(A1:A10, 0))

Solution

1. Step 1: Check INDEX syntax

   INDEX(range, row_number) returns value at row_number in range.

2. Step 2: Check MATCH syntax

   MATCH(lookup_value, lookup_range, 0) finds exact position of lookup_value in lookup_range.

3. Step 3: Combine correctly

   Use MATCH to find row in A1:A10, then INDEX to get value from B1:B10 at that row.

4. Final Answer:

   =INDEX(B1:B10, MATCH("Apple", A1:A10, 0)) -> Option C

5. Quick Check:

   INDEX(B, MATCH(value, A)) correct syntax [OK]

Hint: MATCH finds row, INDEX returns value at that row [OK]

Common Mistakes:

• Swapping INDEX and MATCH arguments
• Using MATCH on wrong range
• Incorrect order of parameters

3. Given the data:
A1:A4 = {"Cat", "Dog", "Bird", "Fish"}
B1:B4 = {5, 3, 8, 2}
What is the result of =INDEX(B1:B4, MATCH("Bird", A1:A4, 0))?

medium

A. 3

B. 8

C. 2

D. 5

Solution

1. Step 1: Find position of "Bird" in A1:A4 using MATCH

   "Bird" is at position 3 in A1:A4.

2. Step 2: Use INDEX to get value at position 3 in B1:B4

   Value at B3 is 8.

3. Final Answer:

   8 -> Option B

4. Quick Check:

   INDEX(B, MATCH("Bird", A)) = 8 [OK]

Hint: MATCH finds row 3, INDEX returns B3 = 8 [OK]

Common Mistakes:

• Confusing row numbers
• Using wrong ranges
• Assuming MATCH returns value

4. You wrote the formula =INDEX(B1:B5, MATCH("Orange", A1:A5, 0)) but it returns an error. What is the most likely cause?

medium

A. The value "Orange" is not found in A1:A5

B. The ranges B1:B5 and A1:A5 have different sizes

C. INDEX cannot be combined with MATCH

D. MATCH requires a third argument of 1 or -1

Solution

1. Step 1: Understand MATCH error

   MATCH returns #N/A error if lookup value is not found in the range.

2. Step 2: Check if "Orange" exists in A1:A5

   If "Orange" is missing, MATCH fails and INDEX cannot get a valid row.

3. Final Answer:

   The value "Orange" is not found in A1:A5 -> Option A

4. Quick Check:

   MATCH missing value causes error [OK]

Hint: Check if lookup value exists before using MATCH [OK]

Common Mistakes:

• Assuming INDEX and MATCH can't combine
• Ignoring #N/A from MATCH
• Wrong MATCH match_type argument

5. You have a table with names in A2:A10 and scores in B2:B10. You want to find the score of the name in D2, but if the name is not found, return "Not Found" instead of an error. Which formula correctly does this?

hard

A. =IFERROR(INDEX(B2:B10, MATCH(D2, A2:A10, 0)), "Not Found")

B. =INDEX(B2:B10, MATCH(D2, A2:A10, 0)) & "Not Found"

C. =IF(ISERROR(MATCH(D2, A2:A10, 0)), "Not Found", INDEX(B2:B10, D2))

D. =MATCH(D2, A2:A10, 0) & INDEX(B2:B10, D2)

Solution

1. Step 1: Use MATCH to find position of D2 in A2:A10

   MATCH(D2, A2:A10, 0) finds exact row of the name.

2. Step 2: Use INDEX to get score from B2:B10 at that position

   INDEX(B2:B10, MATCH(...)) returns the score.

3. Step 3: Use IFERROR to catch errors if name not found

   IFERROR(..., "Not Found") returns "Not Found" instead of error.

4. Final Answer:

   =IFERROR(INDEX(B2:B10, MATCH(D2, A2:A10, 0)), "Not Found") -> Option A

5. Quick Check:

   IFERROR wraps INDEX-MATCH to handle missing names [OK]

Hint: Wrap INDEX-MATCH with IFERROR for safe lookup [OK]

Common Mistakes:

• Concatenating strings instead of handling errors
• Using ISERROR with repeated MATCH calls
• Incorrect argument order in MATCH