To find an exact text string in Excel, use MATCH with its third argument set to 0. With the text to find in D2 and the search list in A2:A6, enter =MATCH(D2,A2:A6,0); Excel returns the item’s position within that list, or #N/A if it does not find a match.
What MATCH Does With Text
MATCH searches one row or one column and returns a number indicating where a value occurs in that range. It does not return the matching text itself, nor does it return the worksheet row number unless the range happens to begin in row 1. For a range starting at A2, a result of 3 means the match is the third item in A2:A6, which is cell A4.
The function’s syntax is =MATCH(lookup_value,lookup_array,match_type). The lookup value can be typed directly into a formula or referenced from another cell. The lookup array must be a single row or column, and match_type determines whether Excel looks for an exact or approximate match. For strings, setting match_type to 0 is usually the important step.
For example, if A2:A6 contains Pear, Mango, Apple, Grape, and Lime, and D2 contains Mango, =MATCH(D2,A2:A6,0) returns 2. That is the position within A2:A6, not the worksheet row number 3. This distinction matters when MATCH is combined with other functions or when a result is used to report a location.
Why the Third Argument Matters
Leaving out match_type does not tell Excel to find an exact string. The default is 1, which requests an approximate match and assumes values are sorted in ascending order. If the range is unsorted, or if the goal is to find a particular name, code, or phrase, an omitted argument can produce a misleading result. Use 0 to request an exact match.
The three match_type choices have different purposes:
0finds an exact match. For text, this mode can also use the wildcard characters described below.1, or an omitted argument, finds an approximate match based on ascending order.-1finds an approximate match based on descending order.
Approximate matching is useful in tasks such as finding a bracket or threshold in a deliberately sorted table, but it is not a safe substitute for exact text lookup. If a list is not sorted as required, the result may not be the item a reader expects. For ordinary string searches, make the zero explicit so the formula communicates its intent.
Find a String in a List
A common setup keeps the search term in one cell and the strings being searched in another. Suppose A2:A6 contains Pear, Mango, Apple, Grape, and Lime, while D2 contains Mango. The formula =MATCH(D2,A2:A6,0) returns 2 because Mango is the second item in the selected range. Changing D2 to Grape changes the result to 4 without editing the formula.
Because MATCH returns a relative position, it is often paired with INDEX when a related value is needed. Suppose A2:A6 contains product names in the order Pear, Mango, Apple, Grape, Lime, and B2:B6 contains their prices in the corresponding rows. With the product to find in D2, use =INDEX(B2:B6,MATCH(D2,A2:A6,0)). If D2 is Mango and its price in B3 is 2.50, the formula returns 2.50. INDEX retrieves the value at the position MATCH found.
Keeping the ranges aligned is essential: the first item in the lookup range must correspond to the first item in the return range, the second to the second, and so on. If one range starts on row 2 and the other starts on row 3, the formula can return a valid-looking value from the wrong record. MATCH itself does not check whether two ranges describe the same records.
Use Wildcards for Partial Text
With match_type set to 0, MATCH can search text using wildcards. An asterisk (*) stands for any number of characters, including none; a question mark (?) stands for exactly one character. The tilde (~) escapes a wildcard when it should be treated as a literal character. These patterns are helpful when a full value is not known, but they are not the same as searching for a literal exact string.
Suppose A2:A5 contains Mango juice, Apple, Mango, and Grape, in that order. The formula =MATCH("Mango*",A2:A5,0) returns 1 because Mango juice is the first entry beginning with Mango. The pattern could also match the shorter value Mango, because the asterisk can represent zero characters. MATCH returns the position of the first matching entry in the range.
For a one-character variation, if A2:A5 contains Cat, Cot, Coat, and Dog, =MATCH("C?t",A2:A5,0) returns 1: the question mark matches the single letter a in Cat. It does not match Coat, which has two letters between C and t. If you need to find a literal asterisk in a value, escape it with a tilde in the pattern, such as =MATCH("Rate~*",A2:A5,0).
Is MATCH Case-Sensitive?
No. MATCH with match_type 0 is not case-sensitive, so text such as Mango and MANGO is treated as a match. This is usually convenient for names and labels, but it can be a problem when capitalization distinguishes codes or identifiers. Changing the capitalization of the search term alone will not make MATCH enforce a case-sensitive comparison.
In Microsoft 365 or Excel 2021, a case-sensitive option can be built with EXACT. If A2:A6 contains mango, Pear, Mango, Grape, and Lime, and D2 contains Mango, use =MATCH(TRUE,EXACT(A2:A6,D2),0). EXACT compares each entry with D2 while respecting capitalization; the formula returns 3 because the uppercase M appears in the third item. This is an array-based formula, and current Microsoft 365 and Excel 2021 calculate the comparison across the range.
For a simple test between two cells rather than a search through a list, the EXACT function can also be used directly. With A2 containing Mango and B2 containing MANGO, =EXACT(A2,B2) returns FALSE. By contrast, =A2=B2 returns TRUE because the ordinary equality comparison for text ignores case. Choose the comparison that matches the actual meaning of the data.
Common Reasons a Match Is Not Found
If an apparently identical string produces #N/A, first check for differences that are hard to see. Leading or trailing spaces, repeated spaces, nonbreaking spaces copied from a web page, and other hidden characters can make two entries different. A value displayed as 123 may also be stored as text in one cell and as a number in another. Formatting a cell to look like text does not necessarily change the underlying value.
For ordinary extra spaces and some nonprinting control characters, a helper column can normalize the source text. If A2 contains two leading spaces followed by Mango and several trailing spaces, =TRIM(CLEAN(A2)) returns Mango. CLEAN removes certain nonprinting control characters, while TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. Fill the helper formula down the list and run MATCH against the cleaned results.
TRIM and CLEAN do not remove every unusual character. A nonbreaking space, often encountered in copied web text, may remain. If A2 contains such a character, =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) replaces character 160 with an ordinary space and then trims the result. This assumes the unwanted character is the nonbreaking space represented by CHAR(160); other invisible characters may need their own cleanup.
Also check that the formula searches the intended range and uses 0 as match_type. If the search value comes from a formula, inspect the calculated result rather than only the displayed cell. For diagnostic work, compare lengths with LEN or copy both values into a plain-text editor that makes spacing easier to inspect. Avoid cleaning the original data destructively until you have a backup or have confirmed that the changes preserve meaningful distinctions.
MATCH Compared With XLOOKUP and Find
MATCH is useful when the position itself matters or when a formula needs to pass that position to INDEX. It does not return the matching value from a separate column. In Microsoft 365 and Excel 2021, XLOOKUP can often do both the searching and returning in one formula. With product names in A2:A6, corresponding prices in B2:B6, and Mango in D2, =XLOOKUP(D2,A2:A6,B2:B6,"Not found",0) returns Mango’s price, 2.50 in the example above. The final 0 explicitly requests an exact match; XLOOKUP uses exact matching by default, but stating it can make the formula easier to review.
The worksheet Find command is different. Press Ctrl+F on Windows to search for displayed or stored content interactively; on Mac, use Command+F. Find helps a person locate text, but it does not return a position into a formula or feed a result into another calculation. MATCH is the better fit for a reusable formula, while Find is convenient for a quick manual check. Searching options can affect whether Find looks within formulas or values, so verify the dialog’s settings if a manual search appears to miss an entry.
A Practical Checklist for Reliable Results
Before relying on a string-match formula, work through these checks:
- Confirm the lookup range is one continuous row or column and contains the expected values.
- Use 0 for an exact lookup rather than relying on MATCH’s approximate default.
- Decide whether a wildcard search is intended. A pattern containing * or ? can match more than one literal string.
- Check capitalization requirements. Ordinary MATCH ignores case; use EXACT when case distinguishes values.
- Investigate spaces, hidden characters, and text-versus-number differences if the result is #N/A.
- Remember that the returned number is a position within the lookup range, not automatically a worksheet row number.
These checks also help prevent a quiet error: a formula can return a number without returning the record the user meant. In particular, approximate matching on unsorted data and wildcard patterns that are broader than intended deserve attention before results are used in reports or downstream formulas.
Frequently Asked Questions
How do I match an exact text string in Excel? Use =MATCH(D2,A2:A20,0) when D2 contains the search text and A2:A20 is the list. The zero requests an exact match, and the result is the item’s position within that range. If no match exists, MATCH returns #N/A.
Does MATCH distinguish uppercase and lowercase? No. MATCH treats text case-insensitively, including in exact-match mode. For a case-sensitive search in Microsoft 365 or Excel 2021, use EXACT inside MATCH, as in =MATCH(TRUE,EXACT(A2:A20,D2),0).
Can MATCH find part of a text string? Yes. With match_type 0, use * for any number of characters or ? for one character, for example =MATCH("North*",A2:A20,0). This returns the position of the first entry matching the pattern, not every matching position.
Why does MATCH return #N/A when the text looks the same? The values may differ because of hidden spaces, nonbreaking spaces, other characters, or different underlying types. Confirm the lookup range and exact-match argument, then inspect or normalize the source data. A displayed value can conceal differences that affect comparison.
Does MATCH return the cell address? No. It returns a relative position in the range. If the match is third in A2:A20, MATCH returns 3, and the worksheet cell is A4. Combine MATCH with INDEX to return a corresponding value from another range.
Related
- CAGR Return in Excel: Calculate Investment Growth
- How to Calculate CAGR in Excel for 3 Years
- How to Calculate Row Percentage in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions…
- Excel Loan Payment Formula: How to Use PMTFor a fixed-rate loan with monthly payments, Excel’s PMT function calculates the regular payment. If B1 contains the…
- Excel Function to Extract Part of a String: ExamplesExcel has several functions for extracting part of a string: use LEFT for characters at the start, RIGHT…
- Excel INDIRECT Sheet Name: Build Dynamic Sheet ReferencesTo use a cell’s text as a worksheet name in Excel, combine it with the cell or range…
- Sort Excel and Keep Rows Together Without Data Mix-UpsTo sort Excel data and keep each record together, select the entire data range or click inside an…
- Excel ROUNDUP Examples: 10 Practical Ways to RoundExcel’s ROUNDUP function always rounds a number away from zero, which makes it useful when a decimal result…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.