An #N/A error in the MATCH function means that Excel cannot find your lookup value in the specified range. When using an approximate match (match_type set to 1 or -1), this error frequently occurs even when the search value clearly exists in the list. This happens because types 1 and -1 use binary search logic, which requires data to be sorted in strict ascending or descending order. If the data is unsorted, the binary search algorithm exits along the wrong search path and returns #N/A.
Quick Answer
If you need an exact match, set the third argument of
MATCHto0:=MATCH(lookup_value, lookup_array, 0). If you do require an approximate match, sort your lookup range: sort ascending (A to Z / smallest to largest) for type1, or sort descending (Z to A / largest to smallest) for type-1.
How match_type Dictates Search Logic
The third argument in MATCH defines how Excel searches the target array:
match_type | Search Method | Sorting Requirement | Return Behavior |
|---|---|---|---|
0 | Linear Search | None (Unsorted) | Finds the first exact match. Returns #N/A only if the value does not exist. |
1 (or omitted) | Binary Search | Ascending Order (Lowest to Highest) | Finds the largest value less than or equal to lookup_value. |
-1 | Binary Search | Descending Order (Highest to Lowest) | Finds the smallest value greater than or equal to lookup_value. |
The Omitted Argument Trap
In MATCH, the third argument is optional and defaults to 1.
Many users write:
=MATCH(A2, B2:B100)
assuming that Excel defaults to an exact match. It does not. Omitting the argument forces Excel to run a Type 1 approximate search that requires ascending order. If your list is unsorted, the formula will return unpredictable row positions or fail with #N/A.
For broader lookup comparisons, review #N/A in VLOOKUP: Exact Match (0/FALSE) vs. Approximate Match (1/TRUE).
Why Binary Searches Fail on Unsorted Data
Understanding how binary searches work clarifies why unsorted data breaks approximate lookups.
Instead of scanning every cell from top to bottom (a linear search), a binary search divides the list in half:
- It inspects the middle value of the array.
- If the lookup value is greater than the middle value, the algorithm discards the lower half and inspects the middle of the remaining upper half.
- It repeats this process until it finds a value that meets the match condition.
Searching for 45 in an Unsorted List using Type 1:
Array: [ 10, 50, 20, 45, 80 ]
▲ ▲ ▲
Low Mid High
1. Midpoint is 20.
2. Excel evaluates: Is 45 >= 20? YES.
3. Excel discards the left half [10, 50] and searches [45, 80].
(Notice that 50 was discarded, but if the midpoint landed differently,
the search path would miss the target entirely).
If the list is not sorted, the assumption behind discarding half the array is invalid. The search can easily head down the wrong path, miss the search key, and return #N/A or the wrong row index.
Boundary Failures: Why Type 1 and Type -1 Return #N/A
Even when your data is correctly sorted, approximate matches will return #N/A if your search key falls outside the range boundaries.
Type 1 Boundary Rule (Ascending)
A Type 1 search finds the largest value that is less than or equal to the lookup value.
- If your lookup value is smaller than the first (smallest) value in the sorted list, no valid match exists.
- Excel halts the formula and returns
#N/A.
Sorted Range: [ 100, 200, 300, 400 ]
Lookup Value: 75
Result: #N/A (No value in the array is <= 75)
Type -1 Boundary Rule (Descending)
A Type -1 search finds the smallest value that is greater than or equal to the lookup value.
- If your lookup value is larger than the first (largest) value in the descending list, no valid match exists.
- Excel halts the formula and returns
#N/A.
Sorted Range: [ 400, 300, 200, 100 ]
Lookup Value: 500
Result: #N/A (No value in the array is >= 500)
Step-by-Step Diagnostic Routine
Follow this diagnostic sequence to resolve #N/A errors in MATCH:
Formula returns #N/A
│
├── Step 1: Did you want an exact match?
│ │
│ ├── YES ──> Add 0: =MATCH(val, range, 0)
│ └── NO ──> Proceed to Step 2.
│
├── Step 2: Check match_type and sort order.
│ │
│ ├── Using Type 1 ──> Is range sorted A-Z / Smallest to Largest?
│ └── Using Type -1 ──> Is range sorted Z-A / Largest to Smallest?
│
└── Step 3: Check Boundary Limits.
│
├── Type 1: Is lookup_value < minimum value in range? ──> Causes #N/A
└── Type -1: Is lookup_value > maximum value in range? ──> Causes #N/A
When to Use Approximate vs. Exact Match
- Use Exact Match (
0): Use this for unique identifiers, employee IDs, product SKUs, customer names, and any dataset where items are unsorted or each key must match precisely. - Use Approximate Match (
1or1): Use this for bracket lookups, such as tax tiers, commission schedules, shipping weight brackets, or grade boundaries. Always ensure the threshold table is sorted in ascending order.
To troubleshoot vector lookups with unsorted ranges in legacy functions, see #N/A when using LOOKUP with non-sorted vectors.
Diagnosing Array Failures in INDEX/MATCH
When MATCH is nested inside INDEX, an #N/A error from MATCH cascades through the entire formula:
=INDEX(C2:C50, MATCH(A2, B2:B50))
- Highlight
MATCH(A2, B2:B50)in the formula bar and pressF9. - If the selection evaluates to
#N/A, confirm whether the third argument was omitted. - Change the formula to
=INDEX(C2:C50, MATCH(A2, B2:B50, 0)). - If the error persists, check whether your array boundaries align. For full troubleshooting steps on range alignments, see #N/A in INDEX/MATCH: Row and Column Array Mismatch.
If you need to trace formula dependencies step-by-step to see where intermediate values fail, consult Using the “Evaluate Formula” tool to trace the root of #VALUE!.