If the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH returns the value’s position within that range, counting from 1; the final 0 requests an exact match.
What the MATCH function does
MATCH searches a one-dimensional range—a single row or a single column—and reports where a value occurs in that range. It does not return the matched value itself. That distinction is important: if the fourth cell in the searched column matches, MATCH returns 4, even if that cell happens to be on worksheet row 5.
The function’s three arguments are the value being searched for, the range to search, and the match type. The match type controls whether Excel looks for an exact value or uses an ordered approximate match. For ordinary lookups, use 0. It is safest to include that argument explicitly rather than rely on a default.
MATCH is useful when you need to locate an entry, check whether it exists, or provide a position to another function. For example, it can find a product code in a list, locate a heading in a row, or help INDEX return a value from a related column. It works with text, numbers, dates, and cell references, provided the compared values have compatible types.
Build an exact-match formula
Suppose product codes are in A2:A100 and the code to search for is entered in E2. In that layout, =MATCH(E2,$A$2:$A$100,0) looks for E2 in the product-code list. If the code is the fourth entry in the range, the result is 4. If it is not present, Excel returns #N/A.
To set it up in Microsoft Excel for Microsoft 365 on Windows:
- Enter or identify the value you want to find, such as a code in E2.
- Select the result cell and enter the MATCH formula, using the correct lookup cell and search range.
- Include 0 as the third argument for an exact match, then press Enter.
- Copy the formula down if each row has a different lookup value. Use dollar signs around the search range so it stays fixed as the formula is copied.
The dollar signs in $A$2:$A$100 make both endpoints absolute references. Without them, a copied formula may shift the search range—for example, from A2:A100 to A3:A101. The lookup reference E2 is relative, so it changes to E3 when copied down, which is usually what you want. Press F4 while editing a reference on Windows to cycle through reference styles; on Mac, the equivalent shortcut is usually Command+T, though keyboard mappings can vary by Excel settings.
Position in a range versus worksheet row
A frequent source of confusion is treating MATCH’s answer as an Excel row number. MATCH counts from the start of the range. If the range starts at row 2 and the match is in its fourth cell, MATCH returns 4, while the worksheet row is 5. If you need the sheet row and the search range begins at A2, add the one-row offset.
With the lookup value in E2 and the search range A2:A100, =MATCH(E2,$A$2:$A$100,0)+1 returns 5 when E2 matches the fourth item in the range. The +1 accounts for the fact that the range begins on worksheet row 2 rather than row 1. If your range starts on another row, adjust the offset accordingly: the worksheet row equals the relative position plus the starting row number minus 1.
If the actual matched value is what you need, not its position, use a retrieval function instead. MATCH can still be part of that solution, but by itself it never returns the corresponding entry from a different column.
Choose between MATCH, XLOOKUP, and INDEX with MATCH
For a simple existence or position check, MATCH is direct and widely compatible. For retrieving a value from a related column, XLOOKUP is often easier to read because it accepts a lookup range and a return range separately. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; files opened in older Excel versions may not calculate it.
With product codes in A2:A100, descriptions in B2:B100, and the code to find in E2, =XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"Not found",0) returns the matching description or the text Not found. For example, if E2 is the code on the fourth list row and B5 contains “Desk lamp,” the result is Desk lamp. The last 0 specifies exact matching, although XLOOKUP uses exact matching by default.
INDEX combined with MATCH is another option, especially when a workbook needs to support older Excel editions that do not include XLOOKUP. With the same code, description, and lookup-cell layout, =INDEX($B$2:$B$100,MATCH(E2,$A$2:$A$100,0)) returns the description corresponding to the matched code. MATCH supplies the position within A2:A100, and INDEX uses that position in B2:B100. Unlike the XLOOKUP example, this version returns an error if the code is missing unless you add error handling.
For newer Excel versions, XMATCH is a successor to MATCH with additional search options and exact match as its default. It is available in Microsoft 365 and Excel 2021 or later. If you specifically need a position and want to work with modern options, consider XMATCH; for a straightforward workbook that may be shared with older Excel users, MATCH with a final 0 is a dependable choice.
Match type, text, and duplicate values
With MATCH, 0 means exact match. The other match types are approximate and depend on the ordering of the search range: 1 finds the largest value less than or equal to the lookup value and expects ascending order; -1 finds the smallest value greater than or equal to it and expects descending order. If the range is not sorted as required, an approximate result may be wrong or unpredictable. Use 0 unless an ordered approximate lookup is intentional.
Text matching with MATCH is not case-sensitive, so “North” and “NORTH” are treated as equivalent for an exact match. If the range contains the same value more than once, MATCH returns the position of the first occurrence it finds. It does not report every duplicate. To count occurrences instead, use a counting function; to retrieve all matching rows, a dynamic-array approach may be more appropriate.
In a layout with lookup values in A2:A100 and the value to check in E2, =COUNTIF($A$2:$A$100,E2)>0 returns TRUE if E2 occurs at least once and FALSE if it does not. For instance, if the value appears twice, COUNTIF returns 2 internally and the comparison still returns TRUE. This checks existence but does not identify the position.
In an exact MATCH lookup, the asterisk (*) and question mark (?) can act as wildcards in text, representing multiple characters and one character respectively. Put a tilde before a wildcard character to search for it literally. A wildcard can be useful for deliberately partial searches, but it can also produce an unintended match when the lookup text contains those characters.
Search across a row or find a column heading
MATCH works horizontally as well as vertically. To find which position a heading occupies across a row, provide a one-row range instead of a one-column range. For example, with headings in A1:Z1 and a heading to find in B4, =MATCH(B4,$A$1:$Z$1,0) returns the heading’s position from left to right. If the match is in D1, the result is 4 because D1 is the fourth cell in A1:Z1.
This can help build flexible reports where a formula needs to locate a month or category heading before retrieving the value beneath it. A common two-way lookup combines one MATCH for the row label and another for the column heading with INDEX. The key idea is to make each search range one-dimensional: one range identifies a row position, and the other identifies a column position. Keep the data and heading ranges aligned so that the positions point to the intended intersection.
Common MATCH errors and how to fix them
#N/A means Excel did not find a suitable match. First confirm that the lookup value really appears in the selected range and that the range includes all relevant rows. Then check for extra spaces, inconsistent punctuation, or values stored as different types. A number stored as text may look like a number but fail to match a numeric cell. Dates can also be affected if one value is a true Excel date and another is text that merely looks like one.
Check that the formula uses the intended lookup cell and search range, and that exact-match formulas end in 0. When copying formulas, inspect whether the range has shifted; absolute references prevent that. If you suspect unwanted spaces in imported text, clean the source data rather than hiding the issue with increasingly complicated lookup formulas. TRIM can remove ordinary leading and trailing spaces in a separate cleanup step, but some imported nonbreaking spaces require additional handling.
If a missing value should display a friendly message, wrap MATCH in IFNA. With the lookup value in E2 and the search range A2:A100, =IFNA(MATCH(E2,$A$2:$A$100,0),"Not found") displays Not found when there is no exact match and otherwise returns the position. IFNA handles the not-found error specifically; IFERROR also catches other error types, which can conceal problems that you may prefer to notice while checking a workbook.
A result that looks like the wrong position is often not an error: it may be a relative position rather than a worksheet row, or it may be the first of several duplicates. Confirm which output you need before changing the formula. If you want a corresponding description, use XLOOKUP or INDEX with MATCH; if you want every occurrence, a first-match function alone is not sufficient.
Frequently Asked Questions
How do I match a value in an Excel column?
Use MATCH with the lookup value, the column range, and 0 for exact matching. For example, when a value is in E2 and the list is A2:A100, the exact-match formula returns its position within that list.
Why does MATCH return 4 instead of row 5?
MATCH returns a position counted from the first cell in its search range. If the range starts in row 2, its fourth item is on worksheet row 5. Add the appropriate starting-row offset only when you specifically need the worksheet row number.
How can I return a value from another column?
Use XLOOKUP to search one column and return a value from another. If the workbook must work in an older Excel version without XLOOKUP, INDEX with MATCH is a common alternative.
Does MATCH distinguish uppercase and lowercase?
No. MATCH is not case-sensitive, so capitalization alone does not separate two text values. A case-sensitive lookup needs a different approach, such as combining EXACT with an array calculation.
How do I check whether a value exists without getting its position?
COUNTIF can count how many times a value appears, and comparing that count with zero gives a TRUE or FALSE existence test. Use MATCH instead when you need the position of the first match.
Can MATCH find a value across a row?
Yes. Supply a horizontal range containing one row, such as a series of column headings. MATCH then returns the position from left to right rather than from top to bottom.
Related
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS Excel Less Than: Criteria and ExamplesTo sum values in Excel only when a related value is less than a limit, use SUMIFS with…
- Calculate Age in Excel Using Date of Birth with DATEDIFTo calculate age in Excel from a date of birth, put the birth date in A2 and enter…
- Extract Text in an Excel Cell: Formulas and Steps That WorkTo extract part of a cell’s text, use a formula such as TEXTBEFORE for text before a separator,…
- IF Function in Excel: Examples and Practical Uses ExplainedThe Excel IF function checks whether a condition is true and returns one result if it is, and…
- Excel Day Name from Date: Formulas, Formats, and FixesTo show the day name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.