Excel MATCH Function: Find a Value’s Position

Formulas & functions · Updated November 18, 2026

Excel’s MATCH function finds a value in a range and returns its relative position, not the value itself. For example, if the value is the third item in a lookup range, MATCH returns 3. If “pl” means Polish-language Excel, the localized function name is PODAJ.POZYCJĘ; the examples below use English function names and comma separators in Microsoft Excel for Microsoft 365 on Windows.

What MATCH Does

MATCH is a lookup function for locating an item within a single row or column. Its result is a position counted from the beginning of the range you give it. That makes it useful when you need to know where a name, product code, date, or number appears, or when another function needs that position to retrieve related information.

For example, suppose a product code is the third cell in A2:A6. MATCH returns 3, even though that cell is physically on worksheet row 4. The function counts from the start of the supplied range, not from row 1 of the worksheet. This distinction matters when you combine MATCH with functions such as INDEX.

MATCH searches only one row or one column at a time. It does not return a whole record, and it does not automatically tell you which worksheet row contains a match unless you account for the range’s starting row. For looking up a value in a table and returning a related result, MATCH is commonly paired with INDEX or replaced by a suitable modern lookup function.

Exact-Match Searches

For most everyday lookups, use match type 0. This tells MATCH to look for an exact match and avoids requiring the list to be sorted. If there is no matching item, Excel returns #N/A. Exact matching is a sensible default for customer names, order numbers, product IDs, and other values that should correspond precisely.

Assume product codes are in A2:A6, in this order: P-1015, P-1028, P-1042, P-1059, and P-1081. To find P-1042, enter =MATCH("P-1042",A2:A6,0). The result is 3 because P-1042 is the third item in that range. If the code is not present, the result is #N/A rather than a blank or zero.

Text matching with match type 0 is not case-sensitive: Excel treats “abc” and “ABC” as the same for this purpose. If case matters, MATCH alone is not enough; a case-sensitive method needs additional functions. Also check that values that look identical really have the same underlying content. A trailing space, an unexpected character, or a number stored as text can prevent an exact match.

The Three Match Types

The third argument controls how MATCH searches. It is optional, but it is best to include it explicitly so the formula’s behavior is clear. If you omit it, Excel uses 1, which means approximate matching—not exact matching.

  • 0: Finds an exact match. The lookup range does not need to be sorted. If there are repeated matches, MATCH returns the position of the first one it encounters.
  • 1: Finds the largest value that is less than or equal to the lookup value. The lookup range must be sorted in ascending order, from smallest to largest.
  • -1: Finds the smallest value that is greater than or equal to the lookup value. The lookup range must be sorted in descending order, from largest to smallest.

For an ascending approximate example, assume the thresholds 0, 50, 70, 90, and 100 are in B2:B6, sorted from smallest to largest. Enter =MATCH(75,B2:B6,1). The result is 3: 70 is the largest threshold that does not exceed 75, and 70 is third in the range. This can help assign a score to a band, but the sorted order is essential. An unsorted range can produce an incorrect-looking result without a clear warning.

For a descending example, assume 100, 80, 60, and 40 are in C2:C5, sorted from largest to smallest. Enter =MATCH(70,C2:C5,-1). The result is 2 because 80 is the smallest listed value that is at least 70, and it occupies the second position. If approximate matching is not genuinely needed, choose 0 instead; approximate modes are easy to misuse when a list is unsorted.

Using MATCH with INDEX

MATCH becomes especially useful when paired with INDEX. MATCH identifies the relative position of a lookup item; INDEX uses that position to return a value from a corresponding range. This two-function approach is a flexible way to look up a result in a separate column.

Assume product codes are in A2:A6 and their prices, in the same row order, are in B2:B6. If P-1042 is the third code and its price is 18.50, enter =INDEX(B2:B6,MATCH("P-1042",A2:A6,0)). The result is 18.50: MATCH returns position 3, and INDEX returns the third value in B2:B6. The two ranges must be aligned and cover the same number of rows for the intended correspondence to hold.

This construction can keep a lookup working when columns are inserted between the lookup column and the result column, because each range is named directly. It can also look to the left: the lookup range and the return range do not have to be arranged in the left-to-right order required by some older lookup approaches. However, it is not automatically better than every alternative. In Microsoft 365, XLOOKUP is often a more readable choice for straightforward lookups, while INDEX and MATCH remain useful in older workbooks and in more adaptable models.

Finding Text with Wildcards

With match type 0, MATCH can use wildcards for text searches. An asterisk (*) represents any number of characters, while a question mark (?) represents one character. To search for a literal asterisk or question mark, put a tilde (~) before it. Wildcards are useful when you know only part of a label, but they can also match more entries than intended.

Assume department names are in A2:A5, with entries such as East Sales, North Support, North Sales, and West Sales. Enter =MATCH("North*",A2:A5,0) to find the first entry that begins with North. The result is 2 because North Support is the second item. If both North Support and North Sales are present, MATCH returns the first matching entry in the range, not every match. To identify all matching rows, use a different approach, such as filtering the data.

Wildcards apply to text lookups, not as a general substitute for approximate numeric matching. If a search unexpectedly finds the wrong text item, inspect both the wildcard pattern and the order of entries: the first matching result is what MATCH returns.

MATCH Compared with XMATCH

XMATCH is a newer alternative available in Microsoft 365 and Excel 2021 or later. Like MATCH, it returns a position, but its default is exact matching, which makes a simple lookup less likely to behave unexpectedly. XMATCH also offers options for searching from the last item toward the first and for using binary search on sorted data.

Assume names are in A2:A6 and Mina is the fourth name in the range. Enter =XMATCH("Mina",A2:A6). The result is 4; XMATCH uses exact matching by default. With MATCH, the comparable exact-match formula needs a 0 as its third argument. If a workbook must remain compatible with older Excel editions that do not support XMATCH, MATCH is the safer choice.

Both functions return a position, not the matching cell’s content. For a reader opening a shared workbook, compatibility matters as much as convenience: a newer function may display an unsupported-function error in an older Excel version. Check the Excel versions used by everyone who needs the workbook before replacing established formulas.

Common MATCH Problems

#N/A: With exact matching, this usually means Excel cannot find an identical item in the supplied range. Check spelling, spaces, punctuation, and whether one value is stored as text while the other is numeric. You can also confirm that the lookup range actually includes the expected cell.

A plausible but incorrect position: Review the third argument. If it is missing or set to 1, MATCH is using ascending approximate matching. Also check the sort order: 1 requires ascending order, and -1 requires descending order. Approximate matching on unsorted data may return a misleading position.

The wrong row is returned by a combined formula: Make sure the INDEX return range and MATCH lookup range begin on corresponding rows and have compatible lengths. MATCH returns a position relative to its own lookup range. If one range starts at row 2 and another starts at row 3, using the position directly can point to the wrong record.

Duplicates: With an exact search, MATCH returns the first matching item in the lookup range. If duplicate IDs should not exist, consider checking the source data for duplicates rather than assuming MATCH will select the intended record.

Numbers and dates: A date is stored by Excel as a number, but formatting can make values look different from their underlying contents. Likewise, an ID entered as text may not match the same digits stored as a number. Compare the source values and their data types before changing the formula.

Practical Tips for Reliable Formulas

  • Specify the match type explicitly. Use 0 for an exact lookup unless approximate matching is deliberate.
  • Keep approximate lookup lists in the required sort order and document why that order matters.
  • Use clearly bounded ranges, such as A2:A500, when practical. Whole-column references can be convenient, but can make a workbook harder to inspect and maintain.
  • When combining MATCH and INDEX, verify that the lookup and return ranges line up item by item.
  • Test a normal match, a value that is absent, and—if relevant—a duplicate or boundary value.
  • For a polished report, decide how missing values should appear. For example, IFNA can handle #N/A, but choose a replacement message carefully so a missing record is not mistaken for a genuine blank result.

For a small list, MATCH can also be used on its own to report a position to the user. For a larger worksheet, it is usually more helpful as one part of a lookup or validation formula. The key question is whether you need the location of an item, the item’s related data, or every record that meets a condition; those are different tasks.

Frequently Asked Questions

Does MATCH return a value or a position?
MATCH returns a relative position within the range you supplied. To return a corresponding value from another range, pair it with INDEX or use a suitable lookup function.

How do I make MATCH find an exact match?
Set the third argument to 0. For example, if the lookup value is in D2 and the list is A2:A20, use =MATCH(D2,A2:A20,0). The result is the position within A2:A20, or #N/A if there is no match.

Why does MATCH return #N/A?
Excel may not find an exact match because of a typo, extra space, different data type, or a lookup range that excludes the value. Check the underlying contents rather than relying only on how cells look when formatted.

Does MATCH search from the bottom?
Traditional MATCH searches from the beginning of the supplied range. XMATCH supports search options that can search in reverse, but it is available only in Microsoft 365 and Excel 2021 or later.

What is MATCH called in Polish Excel?
In Polish-language Excel, the localized name is PODAJ.POZYCJĘ. Function names and argument separators can vary with Excel’s language and regional settings; the examples here show English names and commas.

When to Use MATCH

Use MATCH when the position of an item matters, or when another function needs that position to retrieve a related result. For exact lookups, set the match type to 0; reserve 1 and -1 for deliberately sorted approximate searches. If you remember that MATCH returns a position rather than the matched value, and check that related ranges are aligned, the function is straightforward to use in both simple worksheets and larger lookup formulas.

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.