To check whether a cell’s value appears in a list, use =COUNTIF($D$2:$D$10,A2)>0, assuming the value to check is in A2 and the list is in D2:D10. The formula returns TRUE when Excel finds a match and FALSE when it does not; COUNTIF is usually the simplest choice for a whole-cell match.
The basic COUNTIF formula
COUNTIF counts cells in a range that meet a criterion. Adding a greater-than-zero test turns that count into a logical result: at least one match means TRUE; no matches means FALSE. The dollar signs keep the list range fixed if you copy the formula down, while A2 changes to A3, A4, and so on.
With the value to check in A2 and the candidate list in D2:D10, enter this formula in B2:
=COUNTIF($D$2:$D$10,A2)>0
If “Pear” appears anywhere in D2:D10, the result is TRUE. If it does not, the result is FALSE. COUNTIF counts the matching entries, so duplicates in the list do not affect the yes-or-no answer. For example, two occurrences still produce TRUE. If you want to see the count rather than a logical result, remove the comparison to zero: with the same cell layout, use =COUNTIF($D$2:$D$10,A2).
How to set up and copy the check
Arrange the data so that the item being tested is in one column and the allowed or reference values are in another. Put the check formula beside the first item, then fill it down for the remaining rows. In Excel for Microsoft 365 on Windows, you can drag the fill handle at the lower-right corner of the formula cell, or select the destination cells and use Home > Fill > Down.
- Enter the value to test in A2 and the list of acceptable values in D2:D10.
- Put the formula in B2. The fixed dollar signs in $D$2:$D$10 make every copied formula use the same list.
- Copy or fill the formula down. The reference A2 is relative, so the next row checks A3 against the same list.
- Filter or sort the TRUE/FALSE results if you need to review which entries are present or missing.
If the list will grow, consider putting it in an Excel table. Table columns expand when you add rows, and structured references can make formulas easier to read. For example, with the value in A2 and a table column named Allowed in a table named Table1, use =COUNTIF(Table1[Allowed],A2)>0. The table and column names must match the names in your workbook.
Return a useful message instead of TRUE or FALSE
TRUE and FALSE are useful for calculations and filtering, but a worksheet may be easier for other people to read if it says “Found” or “Not found.” With the value in A2 and the list in D2:D10, enter:
=IF(COUNTIF($D$2:$D$10,A2)>0,"Found","Not found")
This still checks membership using COUNTIF, while IF chooses which text to display. You can replace the messages with labels that suit the task, such as “Approved” and “Review.” The text values need straight double quotation marks in the formula. If you want to use the result in another calculation, the TRUE/FALSE version may be more convenient than text.
A related need is to check whether each item in a column appears in a list, rather than checking just one cell. With items in A2:A100 and the reference list in D2:D10, put the basic formula in B2 and fill it down. Each row is tested independently; the formula does not require a separate formula for every item.
Exact match, case sensitivity, and newer alternatives
COUNTIF checks for a matching cell value, not merely whether the value appears somewhere inside a longer cell. For instance, searching for “Pear” does not normally treat “Green Pear” as the same full value. COUNTIF is not case-sensitive, however, so “pear” and “Pear” are treated as matches. For most lists of names, codes, categories, or products, that behavior is appropriate.
If you have Excel for Microsoft 365 or Excel 2021 and want a lookup-style alternative, XMATCH can return the position of a match. With the item in A2 and the list in D2:D10, use:
=ISNUMBER(XMATCH(A2,$D$2:$D$10,0))
The final 0 requests exact-match mode, and ISNUMBER converts XMATCH’s numeric position into TRUE or FALSE. XMATCH is available in Microsoft 365 and Excel 2021; it is not available in some older perpetual Excel releases. Like COUNTIF, XMATCH is not a case-sensitive test by default. For a basic yes-or-no check, COUNTIF is often shorter and works in more versions.
For a case-sensitive check, use EXACT, which compares text with attention to letter case. With A2 as the value and D2:D10 as the list, use:
=SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0
EXACT compares A2 with each cell in the list, and SUMPRODUCT adds the TRUE results after the double unary operator converts them to 1 and 0. The formula returns TRUE only if at least one entry matches with the same capitalization. For example, “Pear” matches “Pear” but not “pear.” Case sensitivity is useful for identifiers where capitalization is meaningful, but it is usually unnecessary for ordinary words.
When the list contains wildcard characters
COUNTIF recognizes the asterisk (*) as a wildcard for any number of characters and the question mark (?) as a wildcard for one character. A tilde (~) is used to escape a wildcard when it should be treated literally. This means the basic formula has an important edge case: if A2 itself contains * or ?, COUNTIF may interpret that character as a pattern rather than as an ordinary character in the value.
If your list can contain literal asterisks or question marks and you need a literal equality check, use an equality comparison across the list instead. With the value in A2 and the list in D2:D10, use:
=SUMPRODUCT(--(A2=$D$2:$D$10))>0
This tests equality cell by cell rather than passing A2 as a COUNTIF criterion. It remains case-insensitive, like the basic formula. For case-sensitive literal text, use the EXACT formula above. If the values are ordinary text without wildcard characters, COUNTIF is simpler and easier to maintain.
Blank cells, spaces, and values stored differently
A blank lookup cell can produce a surprising result. COUNTIF can count blank cells in the list, so if A2 is empty and D2:D10 includes an empty cell, the basic formula can return TRUE. If an empty item should always count as “not found,” add a check that A2 is not blank. With A2 as the item and D2:D10 as the list, use:
=AND(A2<>"",COUNTIF($D$2:$D$10,A2)>0)
This returns FALSE for a blank A2 and otherwise performs the membership check. It treats a cell containing a formula that returns an empty string as blank for this test. Do not add the blank guard if blank is itself a valid entry that should match a blank in the list.
Another common cause of a missing match is extra spaces. A value that looks like “Pear” may actually contain a leading or trailing space, or a copied value may contain a nonbreaking space that TRIM does not remove. Compare the source and list values, and clean the data if appropriate. With the value in A2 and list entries in D2:D10, a quick check for ordinary leading or trailing spaces is to select the cells and use Excel’s formula bar to inspect their contents; TRIM can help normalize regular spaces in a helper column. Do not silently trim data if spaces are meaningful in the identifiers.
Check that corresponding values use compatible data types as well. A number such as 25 and text that looks like the number “25” can behave differently in comparisons, especially when data has been imported. A cell formatted to display two decimal places is not necessarily stored as text, and formatting alone does not change its underlying value. Standardize the source and list—for example, convert both to numbers or both to text—rather than relying on appearance.
Highlight matching cells with conditional formatting
If the goal is to see matches rather than create a TRUE/FALSE column, conditional formatting can color the cells that appear in the reference list. Select the cells to check, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2 as the top-left selected cell and the list in D2:D10, enter:
=COUNTIF($D$2:$D$10,A2)>0
Choose a format and confirm the rule. Keep the list range absolute, but leave A2 relative so Excel adjusts the row for each cell in the selected range. If you select A2:A100 before creating the rule, the formula should refer to A2, not A1 or A100. The rule highlights matches but does not alter cell values, so it can be used alongside a separate formula column. The conditional-formatting workflow is also available in Excel for Mac, though ribbon placement and dialog appearance can vary slightly by release.
Frequently Asked Questions
Does COUNTIF check whether the whole cell matches?
Yes, when the criterion is an ordinary value, COUNTIF checks for a matching cell value rather than a substring inside a longer value. Wildcard characters in the criterion are a special case: * and ? can act as pattern characters.
Is COUNTIF case-sensitive?
No. COUNTIF treats capitalization as equivalent. Use =SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0 when A2 is the value and D2:D10 is the list and capitalization must match exactly.
How do I check whether a value is not in the list?
With the value in A2 and the list in D2:D10, reverse the basic test by using =COUNTIF($D$2:$D$10,A2)=0. TRUE means no match was found; FALSE means the value is present.
Can I check against a list on another worksheet?
Yes. If the list is on a sheet named Lists in D2:D10, qualify the range with the sheet name. With the value in A2, use =COUNTIF(Lists!$D$2:$D$10,A2)>0. If a sheet name contains spaces, enclose it in single quotation marks, for example ‘Allowed Items’!$D$2:$D$10.
Why does the formula say FALSE when the values look identical?
Look for hidden spaces, different data types, misspellings, and—in a case-sensitive formula—different capitalization. Also confirm that the list range includes the row containing the candidate value and that the value is not being interpreted as a wildcard pattern.
Choosing the right approach
Use COUNTIF for a straightforward, case-insensitive membership test and when compatibility with older Excel versions matters. Choose XMATCH when you also need the position of an exact match in Microsoft 365 or Excel 2021. Use EXACT with SUMPRODUCT when capitalization matters, and use conditional formatting when the useful result is visual highlighting rather than another cell’s output.
Before copying a check across a large worksheet, verify it on a known match, a known nonmatch, a blank, and any unusual values such as identifiers containing * or ?. That small check helps confirm that the formula’s definition of “matches” is the one your data actually needs.
Related
- Excel Number Format: How Many Digits Can It Really Show?
- Excel Formatting Keyboard Shortcuts: A Practical Guide
- Excel Format Numbers in Thousands (K)
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Conditional Formatting If: How Rules WorkIn Excel, conditional formatting applies a format when a cell or formula meets a condition—in effect, an “if…
- Excel Compare Columns in Two Sheets: Methods and FormulasTo compare values in the same row, put this formula in a spare column and fill it down:…
- Excel Table Not Showing Filter? Restore the DropdownsIf an Excel table is not showing filter arrows, click inside the table, open the Table Design tab,…
- Recursive LAMBDA in Excel: Examples That WorkRecursive LAMBDA formulas call themselves to repeat a calculation, such as finding a factorial or stepping through a…
- How to Copy a Conditional Formatting Formula in ExcelTo copy a conditional formatting formula in Excel, copy a cell with the rule and use Home >…
- Filter an Inventory Spreadsheet in ExcelTo filter an inventory spreadsheet in Excel, click a cell in the data and choose Data > Filter;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.