lookup_value - Required. The value you want to find (text, number, or logical value).
lookup_array - Required. The range of cells to search (single row or column).
match_type - Optional. 0 = exact match, 1 = largest value ≤ lookup_value (default, requires sorted ascending), -1 = smallest value ≥ lookup_value (requires sorted descending).
| A | B | C | |
|---|---|---|---|
| 1 | Student ID | Name | Position |
| 2 | S001 | Alice | |
| 3 | S002 | Bob | |
| 4 | S003 | Charlie | |
| 5 | Find: S002 | =MATCH("S002", A2:A4, 0) 2 |
Finds exact "Apple" in column A
Returns position number (e.g., 5 if in row 5)
Finds largest value ≤ 75 (sorted ascending)
Returns position of closest match
Find "Apple" position, return value from column B
Complete two-way lookup solution
Finds smallest value ≥ 75 (sorted descending)
For reverse-sorted data
This lookup function combined with INDEX creates the most powerful lookup formula - more flexible than VLOOKUP, works in any direction, and doesn't break when columns are inserted or deleted. This INDEX/MATCH pattern is the industry standard for professional Excel users, financial analysts, and data managers who need reliable lookups that adapt to changing spreadsheet structures. It finds the row position, INDEX retrieves the value from that position. Essential for building dashboards, financial models, HR databases, inventory systems, and any scenario where data structure may evolve over time.
| A | B | C | |
|---|---|---|---|
| 1 | Student ID | Name | Grade |
| 2 | S001 | Alice | 95 |
| 3 | S002 | Bob | 85 |
| 4 | S003 | Charlie | 78 |
| 5 | Lookup S002: | =INDEX(C2:C4, MATCH("S002", A2:A4, 0)) 85 |
Use this lookup function with approximate match (match_type 1) to find where a value falls in a sorted range - perfect for grade brackets, tax tables, pricing tiers, or performance rankings. This function with match_type 1 finds the largest value that is less than or equal to your lookup value, essential for range-based categorization. This technique is fundamental in financial modeling for tiered commission structures, educational systems for grade assignment, sales operations for discount levels, and any business logic requiring range-based classification.
| A | B | C | |
|---|---|---|---|
| 1 | Score Threshold | Grade | Find 85: |
| 2 | 90 | A | |
| 3 | 80 | B | |
| 4 | 70 | C | |
| 5 | Position: | =MATCH(85, A2:A4, 1) 2 |
❌ The Problem:
✅ Solution:
=MATCH("Apple", A:A, 0)Use match_type 0 for exact match when searching unsorted data. This lookup function requires match_type 1 data to be sorted ascending, match_type -1 requires descending. For unsorted data or when you need exact matches, always use 0. Exact match (0) is the safest default for this function - it returns an error if not found rather than silently returning a wrong position.
❌ The Problem:
✅ Solution:
=MATCH(EXACT("apple", A:A), {TRUE}, 0)This lookup function is case-insensitive by default. If you need case-sensitive matching, use an array formula with EXACT function. For most business scenarios, case-insensitive matching is desired, but financial codes, product SKUs, or scientific data may require exact case matching. Understand this behavior to avoid unexpected results.
❌ The Problem:
✅ Solution:
=MATCH(A1, B1:B100, 0) or =MATCH(A1, B1:Z1, 0)This lookup function requires a one-dimensional lookup array - either a single column (B1:B100) or single row (B1:Z1). For two-dimensional lookups, use INDEX with two MATCH functions for row and column positions, or use the modern <Link href="/formulas/xlookup">XLOOKUP</Link> function which handles multi-dimensional searches more elegantly.
Master these MATCH variants:
From basics to advanced - AI generates perfect formulas instantly.