Excel MATCH Lookup Array: How to Use It

Formulas & functions · Updated November 22, 2026

The MATCH function in Excel finds the position of a lookup value within a one-dimensional range or array, and you use it when you need a row or column number rather than the value itself. For example, =MATCH("North", A2:A10, 0) returns the row offset (1 to 9) where “North” first appears in cells A2:A10.

What the MATCH Function Does

MATCH is one of Excel’s oldest lookup functions, introduced in Excel 2007 and still fully supported in Microsoft 365. Its syntax is:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value – the value you’re searching for (text, number, or cell reference).
  • lookup_array – the single row or column you’re searching in. It cannot be a multi-row/multi-column range.
  • match_type – 0 for exact match, 1 for largest value ≤ lookup_value (requires ascending order), -1 for smallest value ≥ lookup_value (requires descending order). Default is 1.

MATCH returns a number that represents the position of the first matching cell in the array. For instance, if your lookup_array is A2:A10, a result of 1 means the match is in A2, 2 means A3, and so on.

How to Use MATCH for Exact Lookups

The most common use is exact matching with match_type = 0. This works for text, numbers, and even wildcards (asterisk, question mark). Suppose you have a product list in A2:A20 and you want to find the position of “Widget”.

With “Widget” in cell D1, enter:

=MATCH(D1, A2:A20, 0)

If “Widget” is in A7, the formula returns 6 (because A2 is position 1). This position is then often used with INDEX to retrieve a value from another column, like =INDEX(B2:B20, MATCH(D1, A2:A20, 0)) to get the price from column B.

For exact match, the lookup_array does not need to be sorted. If no match is found, MATCH returns the #N/A error.

Using MATCH with Approximate Match Types

Match type 1 (default) finds the largest value that is less than or equal to the lookup_value. This requires the lookup_array to be sorted in ascending order. For example, to find a tax rate based on income brackets:

Assume income thresholds in A2:A6 (0, 10000, 20000, 30000, 40000) and rates in B2:B6. With income in D1:

=INDEX(B2:B6, MATCH(D1, A2:A6, 1))

If D1 is 15000, MATCH returns 2 (since 10000 is the largest threshold ≤ 15000), and INDEX returns the rate from B3. Match type -1 works similarly but requires descending order and finds the smallest value ≥ lookup_value.

Approximate matches are faster than exact matches because Excel can use binary search, but you must keep the array sorted. If not sorted, results are unpredictable.

Choosing Between MATCH, XLOOKUP, and VLOOKUP

MATCH is not a standalone lookup for returning values; it’s a position finder. You pair it with INDEX. XLOOKUP (Excel 2021/Microsoft 365) can return values directly and also handle arrays, but MATCH is still useful when you need the position itself, such as in dynamic array formulas or when building complex formulas.

Compared to VLOOKUP, INDEX/MATCH is more flexible because it can look to the left, and MATCH doesn’t require a table structure. However, XLOOKUP is simpler for most tasks. For example, =XLOOKUP(D1, A2:A20, B2:B20) does the same as the INDEX/MATCH example above but in one function.

Use MATCH when:

  • You need the row/column number for another calculation.
  • You’re working in older Excel versions (pre-2021) and need left lookups.
  • You’re building array formulas that require a position.

Common Errors and How to Fix Them

  • #N/A – No match found. Check spelling, extra spaces, or data type (text vs. number). Use =TRIM() or =CLEAN() to clean data.
  • #VALUE! – The lookup_array is not a single row/column, or the lookup_value is longer than 255 characters.
  • Wrong result with approximate match – Your data isn’t sorted. Sort ascending for match_type 1, descending for -1.
  • MATCH returns a position, not the value – Remember, MATCH gives a number. If you expected a value, wrap it in INDEX.

Also, MATCH is case-insensitive by default. If you need case-sensitive matching, use =MATCH(TRUE, EXACT(D1, A2:A20), 0) as an array formula (press Ctrl+Shift+Enter in older versions; in Microsoft 365 it works normally).

Using MATCH with Arrays and Dynamic Arrays

In Microsoft 365, MATCH can work with dynamic arrays. For instance, to find the position of the first value greater than 100 in a column, you can use =MATCH(TRUE, A2:A20>100, 0). This returns the position of the first TRUE in the array of comparisons.

You can also use MATCH with the FILTER function to find positions of multiple matches, though that’s less common. For example, to get all positions where a condition is true, you might use =FILTER(ROW(A2:A20)-1, A2:A20="X") which returns an array of positions.

Note that MATCH only returns the first match. If you need all occurrences, use FILTER or a combination of SMALL and IF.

MATCH in Other Spreadsheet Applications

Google Sheets has a nearly identical MATCH function with the same syntax. LibreOffice Calc also supports MATCH. In all cases, the function works the same way, but the exact match type 0 is recommended for most uses. In Excel for Mac, the function and syntax are identical; the only difference is keyboard shortcuts (e.g., Cmd+Shift+Enter for array formulas).

Performance and Best Practices

MATCH is fast, especially with exact match on small data. For large datasets (100,000+ rows), approximate match with sorted data is faster. To improve performance:

  • Avoid full-column references like A:A; use a defined range like A2:A10000.
  • Use exact match (0) unless you really need approximate.
  • Combine MATCH with INDEX instead of VLOOKUP for large tables.

Also, be aware that MATCH will treat text and numbers differently. A number stored as text won’t match a numeric lookup value. Use =VALUE() or =TEXT() to convert if needed.

Frequently Asked Questions

Can MATCH look up in a two-dimensional range?

No, MATCH only works on a single row or column. For two-dimensional lookups, use INDEX with two MATCH functions, one for rows and one for columns.

What does match_type 0, 1, and -1 mean?

0 = exact match, 1 = approximate match (largest value ≤ lookup_value, requires ascending order), -1 = approximate match (smallest value ≥ lookup_value, requires descending order). Use 0 for most cases.

Why does MATCH return #N/A even though the value appears?

Common causes: extra spaces, different data types (text vs. number), or the lookup_array contains a formula that returns an error. Use TRIM and CLEAN, and check with ISNUMBER or ISTEXT.

Is MATCH case-sensitive?

No, by default it is not. To make it case-sensitive, use =MATCH(TRUE, EXACT(lookup_value, lookup_array), 0) as an array formula.

Can I use wildcards in MATCH?

Yes, with match_type 0 you can use * (any sequence) and ? (single character) in the lookup_value. For example, =MATCH("Wi*", A2:A20, 0) finds the first value starting with “Wi”.

What is the difference between MATCH and XLOOKUP?

MATCH returns a position number; XLOOKUP returns the actual value. XLOOKUP is more versatile and doesn’t require an array, but MATCH is still useful for position-based calculations and in older Excel versions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.