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.