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))<br>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.