MATCH function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the MATCH function do in Excel?

The MATCH function finds the position of a value in a range or array. It tells you where the value is located, not the value itself.

beginner

Syntax of the MATCH function?

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value to find.
lookup_array: The range to search.
match_type: Optional; 1 for less than or equal, 0 for exact, -1 for greater than or equal.

intermediate

What does the match_type argument do in MATCH?

It controls how Excel matches the lookup_value:
- 1 (default): Finds the largest value less than or equal to lookup_value (requires sorted ascending).
- 0: Finds exact match.
- -1: Finds smallest value greater than or equal to lookup_value (requires sorted descending).

beginner

What happens if MATCH can’t find the lookup_value?

MATCH returns the #N/A error, meaning it did not find the value in the lookup_array.

intermediate

How can MATCH be used with INDEX?

MATCH finds the position of a value, and INDEX uses that position to return the actual value from a range. Together, they can look up values dynamically.

What does MATCH(A2, B1:B10, 0) do?

A Finds the exact position of A2’s value in B1:B10

B Finds the largest value less than or equal to A2 in B1:B10

C Finds the smallest value greater than or equal to A2 in B1:B10

D Returns the value from B1:B10 matching A2

Using 0 as match_type means MATCH looks for an exact match of A2’s value in B1:B10 and returns its position.

If MATCH returns #N/A, what does it mean?

A The formula syntax is wrong

B The lookup_value was found multiple times

C The lookup_array is empty

D The lookup_value was not found in the lookup_array

#N/A means MATCH did not find the lookup_value in the lookup_array.

Which match_type requires the lookup_array to be sorted ascending?

A 0

B -1

C 1

D Any match_type

match_type 1 requires the lookup_array to be sorted ascending for correct results.

What position does MATCH return if the lookup_value is the first item in the lookup_array?

A 0

B 1

C The value itself

D #N/A

MATCH returns the position as a number starting at 1 for the first item.

How can MATCH help when combined with INDEX?

A To find the position of a value and then get the value at that position

B To sort data automatically

C To sum values in a range

D To count how many times a value appears

MATCH finds the position, and INDEX uses that position to return the value, enabling dynamic lookups.

Explain how the MATCH function works and what each argument means.

Think about how MATCH finds a value’s place in a list.

Describe a real-life example where you would use MATCH in a spreadsheet.

Imagine looking up a student’s rank or a product’s position in a list.

Practice

1. What does the MATCH function do in Excel?

easy

A. Adds numbers in a range

B. Sorts data alphabetically

C. Finds the position of a value in a list or range

D. Deletes duplicate values

Solution

1. Step 1: Understand the purpose of MATCH

   The MATCH function searches for a value in a range and returns its position number.

2. Step 2: Compare with other options

   Options A, C, and D describe different functions (SUM, SORT, Remove Duplicates), not MATCH.

3. Final Answer:

   Finds the position of a value in a list or range -> Option C

4. Quick Check:

   MATCH locates position [OK]

Hint: MATCH returns position, not the value itself [OK]

Common Mistakes:

• Confusing MATCH with SUM or LOOKUP
• Thinking MATCH returns the value found
• Assuming MATCH sorts data

2. Which of these is the correct syntax for the MATCH function to find an exact match?

easy

A. MATCH(range, value, 1)

B. MATCH(value, range, 0)

C. MATCH(value, range)

D. MATCH(range, value, -1)

Solution

1. Step 1: Recall MATCH syntax

   The correct syntax is MATCH(lookup_value, lookup_array, match_type). For exact match, match_type is 0.

2. Step 2: Check options

   MATCH(value, range, 0) uses correct order and match_type 0. Others have wrong order or missing match_type.

3. Final Answer:

   MATCH(value, range, 0) -> Option B

4. Quick Check:

   Exact match uses 0 as third argument [OK]

Hint: Exact match always uses 0 as third argument [OK]

Common Mistakes:

• Swapping value and range arguments
• Omitting the match_type argument
• Using 1 or -1 for exact match

3. Given the list {10, 20, 30, 40, 50} in cells A1:A5, what is the result of =MATCH(30, A1:A5, 0)?

medium

A. 3

B. 2

C. 30

D. Error

Solution

1. Step 1: Identify the position of 30 in the list

   The list is 10 (pos 1), 20 (pos 2), 30 (pos 3), 40 (pos 4), 50 (pos 5). 30 is at position 3.

2. Step 2: Understand MATCH with exact match

   MATCH returns the position number, so it returns 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   Position of 30 in list = 3 [OK]

Hint: MATCH returns position, not the value itself [OK]

Common Mistakes:

• Returning the value 30 instead of position
• Confusing position with index starting at 0
• Getting error due to wrong match_type

4. What is wrong with this formula: =MATCH(25, A1:A5, 0) if A1:A5 contains {10, 20, 30, 40, 50}?

medium

A. No error, returns 3

B. Syntax error due to missing parentheses

C. Returns 2 because 20 is closest

D. Returns #N/A because 25 is not found

Solution

1. Step 1: Check if 25 exists in the list

   The list has 10, 20, 30, 40, 50. 25 is not present.

2. Step 2: Understand MATCH with exact match

   With match_type 0, if value not found, MATCH returns #N/A error.

3. Final Answer:

   Returns #N/A because 25 is not found -> Option D

4. Quick Check:

   Exact match missing value returns #N/A [OK]

Hint: Exact match returns #N/A if value missing [OK]

Common Mistakes:

• Assuming MATCH returns closest value position
• Thinking MATCH returns syntax error
• Ignoring #N/A error handling

5. You have a sorted list of prices in A1:A10. Which MATCH formula finds the position of the largest price less than or equal to 75?

hard

A. MATCH(75, A1:A10, 1)

B. MATCH(75, A1:A10, 0)

C. MATCH(75, A1:A10, -1)

D. MATCH(A1:A10, 75, 1)

Solution

1. Step 1: Understand match_type for approximate match

   match_type 1 finds largest value less than or equal to lookup_value in ascending sorted list.

2. Step 2: Check options for correct match_type

   MATCH(75, A1:A10, 1) uses 1, correct for this case. MATCH(75, A1:A10, -1) is for descending order, MATCH(75, A1:A10, 0) is exact match, MATCH(A1:A10, 75, 1) has wrong argument order.

3. Final Answer:

   MATCH(75, A1:A10, 1) -> Option A

4. Quick Check:

   Approximate match ascending uses 1 [OK]

Hint: Use 1 for largest value ≤ lookup in ascending list [OK]

Common Mistakes:

• Using 0 for approximate match
• Using -1 for ascending sorted list
• Omitting match_type argument