INDEX and MATCH combination in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find the price of a product using INDEX and MATCH

You have a table with product names in column A and their prices in column B. Which formula correctly returns the price of the product named "Apple"?

Google Sheets

A1:A5 = {"Banana", "Apple", "Orange", "Grape", "Mango"}
B1:B5 = {1.2, 2.5, 1.8, 3.0, 2.0}

A =INDEX(B1:B5, MATCH("Apple", B1:B5, 0))

B =MATCH("Apple", B1:B5, 0)

C =INDEX(A1:A5, MATCH(2.5, B1:B5, 0))

D =INDEX(B1:B5, MATCH("Apple", A1:A5, 0))

💡 Hint

Use MATCH to find the row number of "Apple" in the product list, then INDEX to get the price from the price list.

Option D correctly uses MATCH to find the position of "Apple" in A1:A5, then INDEX returns the price from B1:B5 at that position. Other options either mismatch ranges or use MATCH incorrectly.

❓ query_result

intermediate

Retrieve employee department using INDEX and MATCH

Given a list of employees in column A and their departments in column B, which formula returns the department of "John"?

Google Sheets

A1:A4 = {"Anna", "John", "Mike", "Sara"}
B1:B4 = {"HR", "Finance", "IT", "Marketing"}

A =INDEX(B1:B4, MATCH("John", A1:A4, 0))

B =MATCH("John", B1:B4, 0)

C =INDEX(A1:A4, MATCH("Finance", B1:B4, 0))

D =INDEX(B1:B4, MATCH("John", B1:B4, 0))

💡 Hint

Find the row of "John" in the employee list, then get the department from the department list.

Option A correctly finds "John" in A1:A4 and returns the corresponding department from B1:B4. Other options misuse MATCH or INDEX ranges.

🔧 Formula Fix

advanced

Identify the syntax error in INDEX and MATCH formula

Which option contains a syntax error in the formula to find the price of "Orange"?

Google Sheets

A1:A5 = {"Banana", "Apple", "Orange", "Grape", "Mango"}
B1:B5 = {1.2, 2.5, 1.8, 3.0, 2.0}

A =INDEX(B1:B5, MATCH("Orange", A1:A5, 0))

B =INDEX(B1:B5 MATCH("Orange", A1:A5, 0))

C =INDEX(B1:B5, MATCH("Orange", A1:A5))

D ))0 ,5A:1A ,"egnarO"(HCTAM ,5B:1B(XEDNI=

💡 Hint

Check for missing commas or parentheses in the formula.

Option B is missing a comma between B1:B5 and MATCH function, causing a syntax error. Other options have correct syntax.

❓ optimization

advanced

Optimize formula to avoid volatile functions

You want to find the department of "Sara" without using volatile functions. Which formula is the best choice?

Google Sheets

A1:A4 = {"Anna", "John", "Mike", "Sara"}
B1:B4 = {"HR", "Finance", "IT", "Marketing"}

A =INDEX(B1:B4, MATCH("Sara", A1:A4, 0))

B =VLOOKUP("Sara", A1:B4, 2, FALSE)

C =INDEX(B1:B4, MATCH("Sara", A1:A4))

D =FILTER(B1:B4, A1:A4="Sara")

💡 Hint

Consider which functions are volatile and which are not.

Option A uses INDEX and MATCH which are non-volatile and efficient. VLOOKUP and FILTER are volatile or less efficient. MATCH without 0 is approximate match.

🧠 Conceptual

expert

Understanding behavior of INDEX and MATCH with multiple matches

Given the data below, what will the formula =INDEX(B1:B6, MATCH("Apple", A1:A6, 0)) return?

Data:

A1:A6 = {"Banana", "Apple", "Orange", "Apple", "Grape", "Apple"}

B1:B6 = {1, 2, 3, 4, 5, 6}

A 6

B 4

C 2

D #N/A error

💡 Hint

MATCH returns the position of the first exact match it finds.

MATCH("Apple", A1:A6, 0) returns 2, the position of the first "Apple". INDEX then returns B2 which is 2. It does not return later matches.