To check whether a value in D2 appears anywhere in the list A2:A10, use =COUNTIF($A$2:$A$10,D2)>0. Excel returns TRUE if it finds the value and FALSE if it does not; use the same test inside IF when you want words such as “Found” or “Not found” instead.
The quickest way to check a list
Suppose column A contains a list of products and D2 contains the product you want to look up. Enter the formula below in E2, then fill it down to check additional values in column D.
With products in A2:A10 and the value to check in D2, use =COUNTIF($A$2:$A$10,D2)>0. If A2:A10 contains Apple, Pear, and Grape, and D2 contains Pear, the result is TRUE. If D2 contains Orange and Orange is not in the list, the result is FALSE.
COUNTIF counts cells in a range that meet a condition. The greater-than-zero comparison turns that count into a simple yes-or-no result: one or more matches means TRUE, and zero matches means FALSE. The dollar signs keep the lookup range fixed when you copy the formula down, while D2 changes to D3, D4, and so on.
Return a readable result instead of TRUE or FALSE
TRUE and FALSE are useful in calculations and other formulas, but a status label may be easier to read in a report. Wrap the COUNTIF test in IF to choose the message Excel displays.
With the list in A2:A10 and the item being checked in D2, enter =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). For Pear in D2 and Pear somewhere in A2:A10, the result is Found; for Orange, the result is Not found.
You can replace the two messages with other text, such as “In stock” and “Reorder,” or use a blank result when no match exists. For example, an empty string is written as two quotation marks with nothing between them. Be aware that the formula displays a message; it does not add, remove, or change anything in the original list.
If you need the position of a match rather than just a yes-or-no answer, XMATCH can locate it. With names in A2:A10 and the lookup name in D2, use =XMATCH(D2,$A$2:$A$10,0). If Pear is the third item in that range, the formula returns 3; if it is absent, XMATCH returns the #N/A error. XMATCH is available in Microsoft 365 and Excel 2021 or later.
Return related information from the matching row
Sometimes the question is not only whether an item exists, but what information belongs to it. For instance, a product list may be in column A and its price in column B. XLOOKUP can return the corresponding value, and its optional not-found argument lets you provide a friendly result.
With product names in A2:A10, prices in B2:B10, and the product to find in D2, use =XLOOKUP(D2,$A$2:$A$10,$B$2:$B$10,"Not found"). If Pear is on the fourth row of the worksheet and its price in B5 is 2.5, the formula returns 2.5; if Orange is not listed, it returns Not found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
For a simple membership test, COUNTIF is usually easier to understand. Use a lookup function when you need a value from another column, or when locating a position matters. XLOOKUP searches for an exact match by default, while specifying the match mode can change its behavior; the formula above leaves the default exact-match mode in place.
Check more than one column or use partial matches
If the same set of entries may appear in either of two columns, COUNTIF can test a rectangular range. With values in A2:B10 and the lookup value in D2, use =COUNTIF($A$2:$B$10,D2)>0. If Pear appears in B4, the result is TRUE. This counts matching cells anywhere in the specified rectangle, not just the first column.
A contains test is different: it asks whether the lookup text occurs inside a longer cell entry. With descriptions in A2:A10 and the search text in D2, use =COUNTIF($A$2:$A$10,"*"&D2&"*")>0. If A2 contains “Green pear, ripe” and D2 contains pear, the result is TRUE. The asterisks allow any number of characters before and after the search text.
Partial matching is useful for descriptions, names with added details, or product codes embedded in longer strings, but it can produce unintended hits. Searching for “art,” for example, can also match “carton.” Use the exact-match formula for a list of discrete values when a partial hit would be misleading. Avoid searching for an empty D2 with the partial-match version: an empty search term can match broadly.
Case sensitivity, wildcards, and spaces
COUNTIF is not case-sensitive. If the list contains “Pear,” it counts “pear” as a match. That behavior is suitable for most ordinary lists, but a case-sensitive code or identifier may require a different test.
With case-sensitive values in A2:A10 and the value to check in D2, use =SUMPRODUCT(--EXACT(D2,$A$2:$A$10))>0. If the list contains “Pear” but D2 is “pear,” the result is FALSE; if D2 is “Pear,” it is TRUE. EXACT compares text with case sensitivity, and SUMPRODUCT counts the TRUE results so the formula can test whether any match exists.
COUNTIF also treats the asterisk (*) and question mark (?) as wildcard characters in its criterion. An asterisk can stand for any sequence of characters, and a question mark can stand for one character. Thus, a lookup value that literally contains one of these symbols may behave like a pattern instead of a literal value. If your data uses such symbols and you need a literal comparison, use the case-sensitive EXACT approach above, or carefully escape wildcard characters with a tilde (~) in a COUNTIF criterion.
Invisible spaces are another frequent reason a value appears to be missing. A cell might contain “Pear ” with a trailing space, or text copied from another system might include a nonbreaking space. Check the source cells and compare their contents carefully. TRIM can remove ordinary extra spaces from text, but it does not remove every kind of nonbreaking space automatically. Avoid changing source data until you know whether those spaces are meaningful.
Choose the right formula for the job
For a straightforward yes-or-no check, COUNTIF is concise, widely supported, and easy to copy down. Other formulas can be preferable when the requirement is different, so choose based on the result you actually need.
- Use COUNTIF when you need to know whether a value appears at least once, or how many times it appears.
- Use MATCH when you want the relative position of an item and need compatibility with older Excel versions. With values in A2:A10 and a lookup value in D2,
=ISNUMBER(MATCH(D2,$A$2:$A$10,0))returns TRUE for a match and FALSE if no match is found. The final 0 requests an exact match; wildcard characters in text criteria still need attention. - Use XLOOKUP when you want to return a corresponding value, such as a price or category, rather than a Boolean result. It requires Microsoft 365 or Excel 2021 or later.
- Use EXACT with SUMPRODUCT when capitalization must count as different. This is also useful when a literal comparison is needed for text containing wildcard characters.
COUNTIF and MATCH are generally more compatible with older Excel releases than newer functions such as XLOOKUP and XMATCH. If a workbook will be opened in an older version, check that version’s function support before relying on a newer function. A function that is not recognized may display a #NAME? error.
Use the check to highlight matching cells
A formula can also help make matches visible without adding a status column. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule. Choose “Use a formula to determine which cells to format,” enter a formula that evaluates to TRUE for the cells you want, select a format, and confirm the rule.
For example, to highlight values in A2:A10 that appear in D2, select A2:A10 and use =COUNTIF($D$2,A2)>0 as the conditional-formatting rule formula. If Pear is in both A4 and D2, A4 is highlighted. The reference A2 is relative, so Excel adjusts it for each selected cell; $D$2 stays fixed. This formula checks each list cell against the one lookup value, the reverse of the earlier example where each query in column D was checked against the list.
On Excel for Mac, the same conditional-formatting feature is available from the Home tab, though the dialog layout can differ. Excel for the web also supports conditional formatting, with some differences in the rule-editing interface. Confirm the selected range and the formula’s relative references: a reference anchored to the wrong row is a common reason a rule highlights the wrong cells.
Fix common formula problems
If a formula returns FALSE when you expect TRUE, first confirm that the lookup cell and list range are correct. Check for misspellings, leading or trailing spaces, numbers stored as text, and hidden characters. A numeric 123 and text “123” can behave differently in some comparisons, so make the data types consistent where possible.
Also check the range boundaries. A list that grows beyond A10 will not be included if the formula still refers only to A2:A10. You can extend the range, use an Excel Table with structured references, or choose a sufficiently large range if that suits the workbook. Tables expand as rows are added and make formulas easier to maintain, but avoid including unrelated cells that could create false matches.
When filling a formula down, inspect the dollar signs. In =COUNTIF($A$2:$A$10,D2)>0, the list remains fixed while the query moves. If the query should also stay fixed, change it to $D$2. If the list should move with each row, remove the dollar signs from its references. A formula can be syntactically valid and still answer the wrong question because its references shifted.
Finally, distinguish “appears in the list” from “appears exactly once.” COUNTIF can count duplicates. With the list in A2:A10 and the value in D2, =COUNTIF($A$2:$A$10,D2) returns the number of matching cells: if Pear occurs twice, it returns 2. The Boolean test remains TRUE in that case because at least one match exists.
Frequently Asked Questions
What is the simplest Excel formula to see if a value is in a list?
With the list in A2:A10 and the value in D2, use =COUNTIF($A$2:$A$10,D2)>0. TRUE means Excel found at least one match; FALSE means it found none.
How do I show Yes or No instead of TRUE or FALSE?
With the same layout, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Yes","No"). It displays Yes when D2 is in the list and No when it is not.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive, so “Pear” and “pear” count as the same text. For a case-sensitive test, use EXACT with SUMPRODUCT.
How can I check whether part of a value appears in a list?
With text entries in A2:A10 and the search text in D2, use =COUNTIF($A$2:$A$10,"*"&D2&"*")>0. The asterisks allow other characters around the search text, so partial matches may include unintended results.
Why does my formula say a value is missing when I can see it?
Look for extra spaces, hidden characters, inconsistent data types, or a lookup range that stops before the visible item. Also check whether the value contains * or ?, which COUNTIF can interpret as wildcard characters.
Related
- Excel Find the Minimum Date With Criteria
- Excel Find Maximum Value Based on Criteria with MAXIFS
- Excel MAXIFS: Find the Highest Value by Criteria
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Format A to Z: How to Sort Rows and Lists SafelyTo sort data from A to Z in Excel for Microsoft 365 on Windows, click a cell in…
- Median Formula for Class 10: Examples and StepsThe median is the middle value after data has been arranged in order. For ungrouped data, use the…
- Excel Formula: Years, Months, and Days Between DatesTo calculate complete years, months, and remaining days between dates in Excel, use DATEDIF for the years and…
- Average Days Between Dates in Excel: Formulas & TipsIf your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
- Divide in Excel: Formula and Keyboard TipsTo divide in Excel, type a forward slash (/) between the numbers or cell references in a formula,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.