MATCH function in Excel - Deep Dive

Overview - MATCH function

What is it?

The MATCH function in Excel helps you find the position of a specific value within a range of cells. Instead of returning the value itself, it tells you where that value is located by giving its relative position number. This is useful when you want to know the order or place of an item in a list without searching manually.

Why it matters

Without the MATCH function, finding the position of a value in a list would require manual searching or complex formulas. MATCH saves time and reduces errors by quickly locating items, which is essential for organizing data, creating dynamic formulas, and automating tasks. It makes spreadsheets smarter and more interactive.

Where it fits

Before learning MATCH, you should understand basic Excel formulas and how ranges work. After MATCH, you can learn how to combine it with other functions like INDEX for powerful lookups, or use it in conditional formulas and data validation.

Mental Model

Core Idea

MATCH tells you the position number of a value inside a list or range, like counting steps until you find it.

Think of it like...

Imagine a bookshelf with numbered slots. MATCH is like asking, 'In which slot is my favorite book?' Instead of giving you the book, it tells you the slot number where the book sits.

List: [Apple, Banana, Cherry, Date, Elderberry]
Value to find: Cherry
MATCH result: 3 (because Cherry is the third item in the list)

Build-Up - 7 Steps

1

Foundation

Basic MATCH usage with exact match

Concept: Learn how to find the exact position of a value in a list using MATCH with exact match mode.

Syntax: MATCH(lookup_value, lookup_array, 0) - lookup_value: the value you want to find. - lookup_array: the range or list where you search. - 0 means exact match only. Example: =MATCH("Banana", A1:A5, 0) If A1:A5 contains [Apple, Banana, Cherry, Date, Elderberry], this returns 2.

Result

Returns 2 because Banana is the second item in the list.

Understanding exact match mode is key because it finds the precise value's position, which is the most common use case.

2

Foundation

Understanding lookup_array and relative position

Concept: Learn that MATCH returns the position relative to the start of the lookup_array, not the cell address.

If your lookup_array is B2:B6, and the value is in B4, MATCH returns 3 because B4 is the third cell in that range. Example: =MATCH(50, B2:B6, 0) If B2:B6 = [10, 50, 30, 40, 20], result is 2 because 50 is second in the range.

Result

Returns 2, the relative position inside the range, not the absolute row number.

Knowing MATCH returns relative position helps avoid confusion when combining with other functions like INDEX.

3

Intermediate

Using MATCH with approximate match

🤔Before reading on: Do you think MATCH with approximate match finds the closest smaller or larger value? Commit to your answer.

Concept: Learn how MATCH can find the closest match when exact value is missing, using approximate match mode (1 or -1).

Syntax: MATCH(lookup_value, lookup_array, 1) or MATCH(lookup_value, lookup_array, -1) - 1 finds largest value less than or equal to lookup_value; list must be sorted ascending. - -1 finds smallest value greater than or equal to lookup_value; list must be sorted descending. Example: =MATCH(35, A1:A5, 1) If A1:A5 = [10, 20, 30, 40, 50], returns 3 because 30 is largest <= 35.

Result

Returns 3, the position of 30, the closest smaller value to 35.

Understanding approximate match unlocks powerful uses like range lookups and grading scales.

4

Intermediate

MATCH with text and wildcards

🤔Before reading on: Can MATCH find partial text matches using wildcards? Commit to yes or no.

Concept: Learn how MATCH can find text values using wildcards like * and ? for flexible matching.

Use wildcards in lookup_value with exact match mode (0). - * matches any number of characters. - ? matches exactly one character. Example: =MATCH("B*", A1:A5, 0) If A1:A5 = [Apple, Banana, Cherry, Date, Elderberry], returns 2 because Banana matches B*.

Result

Returns 2, position of Banana which matches the pattern.

Using wildcards with MATCH allows searching for patterns, not just exact values.

5

Intermediate

Combining MATCH with INDEX for lookups

Concept: Learn how MATCH works with INDEX to find and return values dynamically.

INDEX returns a value at a position; MATCH finds that position. Example: =INDEX(B1:B5, MATCH("Cherry", A1:A5, 0)) If A1:A5 = [Apple, Banana, Cherry, Date, Elderberry] and B1:B5 = [100, 200, 300, 400, 500], this returns 300. MATCH finds Cherry at position 3; INDEX returns 3rd value in B1:B5.

Result

Returns 300, the value paired with Cherry.

Combining MATCH and INDEX creates powerful dynamic lookups without needing VLOOKUP.

6

Advanced

Using MATCH for two-way lookups

🤔Before reading on: Can MATCH be used to find both row and column positions for a value? Commit to yes or no.

Concept: Learn how to use MATCH twice to find row and column positions for a value in a table.

Example: To find the value at intersection of 'Banana' in A1:A5 and 'Price' in B1:E1: =INDEX(B2:E5, MATCH("Banana", A2:A5, 0), MATCH("Price", B1:E1, 0)) MATCH finds row and column numbers; INDEX returns the intersecting value.

Result

Returns the value at the intersection of Banana row and Price column.

Using MATCH for both dimensions enables flexible table lookups beyond simple lists.

7

Expert

MATCH with dynamic arrays and spill ranges

🤔Before reading on: Does MATCH support dynamic arrays and spill ranges in modern Excel? Commit to yes or no.

Concept: Learn how MATCH behaves with dynamic arrays and how it can be combined with new Excel functions for advanced formulas.

In Excel with dynamic arrays, MATCH can work with spill ranges (formulas that output multiple values). Example: =MATCH(5, SORT(A1:A10), 0) MATCH finds position in the sorted spill range. You can also combine with FILTER or UNIQUE for complex lookups.

Result

Returns the position of 5 in the sorted list dynamically generated by SORT.

Understanding MATCH with dynamic arrays unlocks modern, efficient spreadsheet designs.

Under the Hood

MATCH scans the lookup_array from start to end (or end to start for -1 mode) comparing each item to the lookup_value. For exact match (0), it stops when it finds the first exact match. For approximate match (1 or -1), it relies on the list being sorted and uses a binary search-like method internally for speed, returning the closest match according to the mode.

Why designed this way?

MATCH was designed to provide a fast, simple way to find positions without returning values, enabling flexible formulas. The choice of match types balances ease of use (exact match) with powerful approximate lookups needed for ranges and thresholds. Sorting requirements for approximate match ensure predictable results and efficient searching.

┌───────────────┐
│ lookup_value  │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ lookup_array (list/range)   │
│ ┌─────┐ ┌─────┐ ┌─────┐     │
│ │ val │ │ val │ │ val │ ... │
│ └─────┘ └─────┘ └─────┘     │
└──────┬──────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ MATCH compares values        │
│ Stops when match found       │
│ Returns relative position   │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does MATCH always return the cell address of the found value? Commit yes or no.

Common Belief: MATCH returns the actual cell address or reference of the found value.

Reality: MATCH returns the relative position number within the lookup_array, not the cell address.

Why it matters: Confusing position with address leads to errors when combining MATCH with other functions expecting positions.

Quick: Can MATCH with approximate match work on unsorted lists? Commit yes or no.

Common Belief: MATCH approximate match works correctly on any list, sorted or not.

Reality: Approximate match requires the lookup_array to be sorted ascending (for 1) or descending (for -1) to work correctly.

Why it matters: Using approximate match on unsorted data causes wrong or unpredictable results, leading to wrong decisions.

Quick: Does MATCH with wildcards work with approximate match mode? Commit yes or no.

Common Belief: MATCH wildcards can be used with approximate match modes (1 or -1).

Reality: Wildcards only work with exact match mode (0). Approximate match ignores wildcards.

Why it matters: Trying to use wildcards with approximate match causes formulas to fail or return wrong positions.

Quick: Does MATCH return the first or last occurrence when duplicates exist? Commit first or last.

Common Belief: MATCH returns the last occurrence of a value if duplicates exist.

Reality: MATCH always returns the position of the first occurrence of the lookup_value.

Why it matters: Expecting the last occurrence can cause wrong data retrieval or logic errors in reports.

Expert Zone

1

MATCH's approximate match uses a binary search internally, making it very fast on large sorted lists compared to scanning each item.

2

When combining MATCH with INDEX, the relative position returned by MATCH must align exactly with the INDEX range to avoid off-by-one errors.

3

MATCH can be combined with array formulas and newer dynamic array functions to create powerful, flexible lookup solutions that adapt to changing data.

When NOT to use

MATCH is not suitable when you need to return the actual value directly; use XLOOKUP or INDEX/MATCH instead. Also, avoid approximate match on unsorted data; use FILTER or other lookup functions for complex criteria.

Production Patterns

Professionals use MATCH combined with INDEX for two-way lookups in dashboards, dynamic reports, and data validation. MATCH is also used in conditional formatting formulas to highlight rows or columns based on position. Advanced users embed MATCH inside LET functions for readability and performance.

Connections

INDEX function

MATCH provides position input to INDEX to retrieve values dynamically.

Understanding MATCH's position output is essential to use INDEX effectively for flexible lookups.

Binary search algorithm

MATCH approximate match mode uses a binary search-like method internally for efficiency.

Knowing MATCH relies on sorted data and binary search explains why sorting is critical for approximate match.

Library cataloging systems

Both MATCH and cataloging systems locate items by position or code rather than content itself.

Recognizing that MATCH finds 'where' something is, not 'what' it is, parallels how libraries organize books by shelf position.

Common Pitfalls

#1 Using approximate match on unsorted data causes wrong results.

Wrong approach:

=MATCH(35, A1:A5, 1) where A1:A5 = [40, 10, 50, 20, 30]

Correct approach:

=MATCH(35, SORT(A1:A5), 1)

Root cause:

Approximate match requires sorted data; unsorted data breaks the search logic.

#2 Expecting MATCH to return cell address instead of position.

Wrong approach:

Using MATCH result directly as a cell reference like =MATCH("Banana", A1:A5, 0) + 1 to get cell B2 (wrong).

Correct approach:

Use INDEX with MATCH: =INDEX(B1:B5, MATCH("Banana", A1:A5, 0))

Root cause:

Confusing position number with actual cell reference.

#3 Using wildcards with approximate match mode.

Wrong approach:

=MATCH("B*", A1:A5, 1)

Correct approach:

=MATCH("B*", A1:A5, 0)

Root cause:

Wildcards only work with exact match mode.

Key Takeaways

MATCH finds the position of a value in a list, not the value itself or its cell address.

Exact match mode (0) finds the precise value; approximate match (1 or -1) finds closest matches but requires sorted data.

MATCH works well with wildcards for flexible text searches but only in exact match mode.

Combining MATCH with INDEX enables powerful dynamic lookups beyond simple searches.

Understanding MATCH's behavior with dynamic arrays and sorting is key for advanced, efficient spreadsheet formulas.