MATCH Type 0 vs 1 vs -1 Explained

MATCH type 0, 1 and -1 are the three settings of MATCH's third argument, and they decide what counts as a match. Type 0 finds an exact match in any order. Type 1 (the default) finds the largest value that is less than or equal to what you're looking for, on a list sorted smallest to largest. Type -1 finds the smallest value that is greater than or equal to it, on a list sorted largest to smallest. This guide is for anyone using INDEX MATCH who wants to know which number to type, and why the wrong one can give a quietly wrong answer.

MATCH types at a glance#

Each match type answers a different question about the same list.

MATCH always returns a position (1 for the first item, 2 for the second…), not the value itself. You usually wrap it in INDEX to bring back a value from that position.

The syntax#

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: what to find.
• lookup_array: a single row or column to search.
• match_type: 0, 1 or -1. If you leave it out, Excel uses 1.

That default is the single most important thing to remember. Most lookups need an exact match, so most MATCH formulas you write should end in ,0). The MATCH function guide covers the basics, and our INDEX MATCH beginner's guide shows how the two fit together.

Match type 0: exact match#

Type 0 looks for a cell that equals the lookup value and returns its position. The order of the list doesn't matter, and the comparison ignores upper and lower case. If several cells match, it returns the first one. If none do, it returns #N/A.

For text, type 0 also accepts wildcards: =MATCH("Gr*",A2:A9,0) finds the first item starting with "Gr". Use type 0 for anything that should match exactly: employee IDs, SKUs, names, month headers in a two-way lookup.

Match type 1: largest value less than or equal#

Type 1 walks down an ascending list and stops at the last value that isn't bigger than the lookup value. This is how you place a number into a band. Here is a commission tier table, sorted smallest to largest:

=MATCH(62000,A2:A5,1)

62,000 is not in the list. The largest value at or below it is 50,000, in position 3. Wrap it in INDEX to get the tier name:

=INDEX(B2:B5,MATCH(62000,A2:A5,1))

The result is Gold. This is exactly what VLOOKUP does with TRUE as its last argument; our guide to VLOOKUP approximate match for tiers and bands uses the same idea.

Match type -1: smallest value greater than or equal#

Type -1 is the mirror image: on a list sorted largest to smallest, it finds the smallest value that is still big enough. A good example is choosing a shipping box. These boxes are listed by capacity, biggest first:

=MATCH(18,C2:C5,-1)

18 kg doesn't fit the Small box (10). The smallest capacity that is at least 18 is 20, in position 3, so =INDEX(D2:D5,MATCH(18,C2:C5,-1)) returns Medium. A 55 kg parcel returns #N/A, because no box is big enough.

The same lookup with all three types#

Here is what happens when you search the tier list in A2:A5 (0, 25,000, 50,000, 100,000) with each type:

That last row is the trap: type 1 and -1 assume a sort order and don't check it. On the wrong order they can return a wrong position or #N/A, and nothing warns you.

When to use each match type#

Choose the match type from the question, not from habit:

• Use 0 whenever the lookup value should appear in the list exactly: IDs, codes, names, column headers. This is the right choice in most INDEX MATCH formulas and in job tests.
• Use 1 for "which band does this number fall into?" questions, with the band start values sorted smallest to largest: commission tiers, grade boundaries, tax brackets, shipping weight bands.
• Use -1 for "what's the smallest option that is big enough?" questions, with the options sorted largest to smallest: box sizes, pack sizes, capacity limits.

MATCH comes up in almost every list of Excel interview questions, and "what does the 0 mean?" is a favourite follow-up.

How XMATCH changes the numbers#

XMATCH, in Excel 2021, Excel 2024 and Microsoft 365, uses a match_mode argument that looks similar but means something different:

So =XMATCH(62000,A2:A5,-1) returns 3, like =MATCH(62000,A2:A5,1), and =XMATCH(18,C2:C5,1) returns 3, like =MATCH(18,C2:C5,-1). If you switch between the two, check the sign every time. Our comparison of MATCH vs XMATCH covers the other differences.

Common mistakes with MATCH types#

These are the slips that cause most MATCH errors:

1. Leaving out the match type. You get type 1, which can return the wrong row on unsorted data.
2. Using 1 or -1 on unsorted data. Sort the list in the direction the type expects, or use XMATCH.
3. Using 0 for bands. An exact match on 62,000 finds nothing in a tier table.
4. Copying XMATCH's -1 into MATCH. The signs are reversed between the two functions.
5. Hidden spaces or numbers stored as text. An exact match fails if "P-101 " has a trailing space. Clean the data first; our comparison of TRIM vs CLEAN and of SUBSTITUTE vs REPLACE shows how.

Our recommendation#

Type 0 by default. Most real lookups are exact, and 0 is the only type that doesn't depend on sort order. Switch to 1 only for band lookups on a list sorted smallest to largest, and use -1 only for the rarer "smallest option that fits" lookup on a list sorted largest to smallest. If you have Excel 2021 or later, XMATCH's modes don't need sorting, but remember the reversed signs.

In short: 0 for exact, 1 for "this band or below", -1 for "this size or above".

Practise it#

Try the tier exercise on this page, which uses INDEX with MATCH type 1. Then open the INDEX and MATCH lesson to build exact-match lookups with 0, including a two-way lookup, and the VLOOKUP approximate match lesson to see band lookups and what happens when someone re-sorts the tier table.