INDEX-MATCH combination 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 a specific row and column in a given range or array.

beginner

What is the purpose of the MATCH function in Excel?

MATCH finds the position of a value in a row or column and returns its relative position number.

intermediate

Why combine INDEX and MATCH instead of using VLOOKUP?

INDEX-MATCH is more flexible because it can look left or right, works with large data, and is less error-prone when columns change.

beginner

Write a simple INDEX-MATCH formula to find the price of an item named "Apple" in a list where A2:A10 has items and B2:B10 has prices.

The formula is: <br> =INDEX(B2:B10, MATCH("Apple", A2:A10, 0)) <br> It finds "Apple" in A2:A10 and returns the price from B2:B10 at the same row.

beginner

What does the '0' mean in the MATCH function inside INDEX-MATCH?

The '0' tells MATCH to look for an exact match of the lookup value.

What does the MATCH function return?

A The position of the lookup value in the range

B The value found in the lookup range

C The sum of values in the range

D The average of values in the range

MATCH returns the position number of the lookup value within the specified range.

Which function returns a value from a specific row and column in a range?

A MATCH

B INDEX

C SUM

D VLOOKUP

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

Why is INDEX-MATCH preferred over VLOOKUP in many cases?

A Because it only works with numbers

B Because it automatically sorts data

C Because it is faster to type

D Because it can look up values to the left and is more flexible

INDEX-MATCH can look left or right and is more flexible when columns change.

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

A Exact match

B Ignore errors

C Approximate match

D Match first letter only

The 0 tells MATCH to find an exact match of "Apple".

If MATCH cannot find the lookup value, what happens?

A It returns the last value in the range

B It returns 0

C It returns #N/A error

D It returns the first value in the range

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

Explain how the INDEX-MATCH combination works to find a value in a table.

Think about how MATCH and INDEX work together step-by-step.

Describe a situation where using INDEX-MATCH is better than VLOOKUP.

Consider limitations of VLOOKUP and how INDEX-MATCH overcomes them.