MATCH function in Excel - Step-by-Step Guide

Introduction

The MATCH function helps you find the position of a value in a list or range. It tells you where something is located, like finding a friend's name in a list of contacts.

When you want to find the row number of a specific product in a product list.

When you need to locate a date in a column of dates to match it with sales data.

When you want to find the position of a student's name in a class roster.

When you want to check if a certain item exists in a list and where it is.

When you want to combine with other functions like INDEX to get related data.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- formula bar

Formula bar shows the formula being typed

💡 Start typing =MATCH(

Step 3: Enter the value to find

- inside the MATCH formula

The value is included as the first argument in the formula

💡 Use quotes for text, e.g., "Apple" or a cell reference like A2

Step 4: Type a comma and select the range to search

- inside the MATCH formula after the value

The range is included as the second argument

💡 Select the cells where you want to search, e.g., B1:B10

Step 5: Type a comma and enter the match type

- inside the MATCH formula after the range

The match type is included as the third argument

💡 Use 0 for exact match, 1 for less than or equal, -1 for greater than or equal

Step 6: Type a closing parenthesis and press Enter

- formula bar

The formula calculates and shows the position number in the cell

Before vs After

Before

A list of fruits in cells A1:A5: Apple, Banana, Cherry, Date, Fig

After

Using =MATCH("Cherry", A1:A5, 0) in cell B1 shows 3 because Cherry is the third item

Settings Reference

Lookup_value

📍 First argument in MATCH formula

The value you want to find in the range

Default: No default, must specify

Lookup_array

📍 Second argument in MATCH formula

The list or range where you search for the value

Default: No default, must specify

Match_type

📍 Third argument in MATCH formula

Defines how Excel matches the value: 1 = less than or equal, 0 = exact, -1 = greater than or equal

Default: 1

Common Mistakes

Using MATCH without setting match_type to 0 for exact match

Excel may return wrong position if the list is not sorted or if you want an exact match

Always use 0 as the third argument for exact match unless you know the list is sorted and want approximate match

Entering a range with multiple columns as lookup_array

MATCH only works with a single row or column range

Select only one row or one column as the lookup_array

Summary

MATCH finds the position of a value in a list or range.

Use 0 as match_type for exact matches to avoid errors.

MATCH works with one row or one column ranges only.

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