MATCH Function in Excel: The Complete Guide with Real Examples

What Is the MATCH Function in Excel?

The MATCH function searches for a specific value in a range of cells and returns the relative position of that value within the range. Think of it as asking Excel: “Where in this list does this value appear?” — and Excel replies with a number like 3, 7, or 12.

On its own, MATCH is very useful. But paired with INDEX or other functions, it becomes one of the most powerful lookup tools Excel has to offer. It works with both vertical columns and horizontal rows, which already gives it an edge over many alternatives.

MATCH Function Syntax

Let’s break each argument down clearly:

The 3 Match Types Explained

This is where many Excel users get tripped up. The match_type argument fundamentally changes how MATCH behaves:

Type 0 — Exact Match (Most Common)

This is the version you’ll use 90% of the time. Set match_type to 0 and Excel finds the first cell that exactly equals your lookup value. Order doesn’t matter.

Example 1: Find a Product’s Row Position

You have a list of products and want to know which row “Keyboard” is in.

The formula returns 3 because “Keyboard” is the 3rd item in the range A2:A5. Note that it’s position within the range, not the actual row number.

Example 2: Match with a Cell Reference

Instead of hardcoding the search value, reference a cell — this makes your formula dynamic.

Change the value in B2, and the MATCH formula updates instantly. Here it returns 4 because Dubai is the 4th item in A2:A6. This is the real power of dynamic referencing.

Example 3: MATCH in a Horizontal Row

MATCH works just as well across columns as it does down rows. Here we’re searching a header row to find which column “March” is in.

Type 1 — Less Than or Equal (Approximate Match Ascending)

With match_type set to 1, MATCH finds the largest value that is still less than or equal to your lookup value. This is perfect for tiered systems — tax brackets, shipping rates, discount tiers, grading scales, and so on.

Important: The lookup array must be sorted in ascending order for this to work correctly.

Example 4: Grade Calculator

Given a student’s score, find which grade bracket they fall into.

A score of 73 returns position 3, pointing to the “70” bracket (Grade C). You’d then use INDEX to pull the actual grade letter — covered below.

Example 5: Shipping Rate Tiers

An order of $85 falls in tier 2 (the $50 bracket), which means $5.99 shipping. Pair this with INDEX to return the actual fee automatically.

Type -1 — Greater Than or Equal (Approximate Match Descending)

Match type -1 is the mirror of type 1. It finds the smallest value that is still greater than or equal to your lookup value. The array must be sorted in descending order.

This is less commonly used, but handy for systems where you work from high to low — like SLA tiers, priority queues, or performance benchmarks.

Example 6: SLA Response Time Tiers

With an actual response time of 5 hours, MATCH returns position 2. In the descending list 24, 12, 4, 1, the smallest value that is still greater than or equal to 5 is 12, so Excel points to the second item. Pair it with INDEX and you can return the actual promise level: Priority.

INDEX + MATCH: The Power Combination

MATCH alone returns a position number. That’s useful, but what you usually want is the actual value at that position. That’s where INDEX comes in. This combo is often called the best lookup formula in Excel — more flexible than VLOOKUP and more intuitive than complex alternatives.

Here’s how it works: MATCH finds the row number, INDEX uses that number to pull the value from a different column.

Example 7: Look Up a Price by Product Name

Example 8: INDEX MATCH — Look LEFT (Something VLOOKUP Can’t Do)

One of VLOOKUP’s biggest limitations is it can only look to the right. INDEX + MATCH has no such restriction. Here we look up a department and return the employee name from the column on the LEFT.

We searched in column C (Department) and returned from column B (Name) — that’s a left-lookup that VLOOKUP simply cannot perform.

Two-Way Lookup with MATCH

Need to look up a value by both a row and a column simultaneously? Use two MATCH functions inside one INDEX formula — one for the row, one for the column. This is incredibly useful for matrix-style data like sales tables, schedules, and pricing grids.

Example 9: Sales Data by Region and Month

Change B6 to “East” and C6 to “Mar” — the formula instantly returns $9,200. No manual lookup needed, ever.

Wildcard MATCH — Partial Text Matching

When you don’t know the exact value but know part of it, MATCH supports wildcard characters. This only works with match_type 0 (exact match mode, but with wildcards).

• * — matches any number of characters
• ? — matches any single character
• ~ — escapes a literal * or ? character

Example 10: Find a Name You Only Partially Know

MATCH with Multiple Criteria

MATCH by itself handles one criteria. But you can combine it with array formulas or helper columns to match on multiple columns at once. The classic approach uses a concatenated array formula (enter with Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365).

This creates an array of 1s and 0s — the product is 1 only where BOTH conditions are true — and then MATCH finds the first 1.

Example 11: Find Employee by Name AND Department

MATCH vs VLOOKUP vs XLOOKUP

A fair question is: why use MATCH at all when VLOOKUP and XLOOKUP exist? The answer is nuance — each tool has a job it does best.

The bottom line: use XLOOKUP if you’re on a modern version of Excel and just need a simple lookup. Use INDEX + MATCH when you need backward compatibility, two-way lookups, or positional logic. Use MATCH alone when you specifically need to know where something is, not what it is.

Common MATCH Errors and How to Fix Them

IFERROR Wrapper — Always a Good Practice

This way, instead of an ugly #N/A error, your users see a friendly message. Clean, professional, and much easier to troubleshoot.

Quick-Reference: MATCH Function Cheat Sheet

Frequently Asked Questions

Final Thoughts

The MATCH function in Excel is one of those tools that quietly earns its place in every serious spreadsheet. It doesn’t dazzle at first glance — it just tells you a number. But that number unlocks the ability to build dynamic, flexible, bulletproof lookup formulas that hold up as your data grows and changes.

Start simple: use MATCH with type 0 to confirm a value exists and find its position. Then graduate to INDEX + MATCH for flexible lookups. Eventually you’ll find yourself reaching for two-way lookups, wildcard searches, and multi-criteria arrays — and wondering how any spreadsheet ever ran without them.

The more you use it, the more situations you’ll find where MATCH is exactly the right tool for the job. It’s not flashy. It’s reliable. And in Excel, reliability is everything.