Excel Formula: Check If a Value Is in a List

Formulas & functions · Updated November 19, 2026

If the value to check is in A2 and your list is in D2:D10, use =IF(COUNTIF($D$2:$D$10,A2)>0,"In list","Not in list"). It returns “In list” when A2 appears in the range and “Not in list” when it does not; replace the cell references and labels with your own.

The quick formula and its layout

The example assumes A2 contains one value to test and D2:D10 contains the allowed values. The dollar signs keep the list range fixed if you copy the formula down a column. If A2 contains Pear and Pear appears anywhere in D2:D10, the formula returns “In list.” If it does not appear, it returns “Not in list.”

If you only need a logical result rather than words, use this version with the same cell layout:

=COUNTIF($D$2:$D$10,A2)>0

It returns TRUE when there is at least one match and FALSE when there is none. That result is useful inside other formulas, such as an IF test, or for filtering and reporting. The greater-than-zero test matters because COUNTIF returns a number: zero for no matches, one for one match, and a larger number if the list contains duplicates.

How COUNTIF checks membership

COUNTIF counts cells in a range that meet a criterion. Here, the criterion is the value in A2, so the formula asks Excel to count how many cells in D2:D10 match that value. Testing whether the count is greater than zero turns the count into a simple yes-or-no result. You do not need to remove duplicates from the list for this check to work.

For example, with Pear in A2 and a list of Apple, Pear, and Plum in D2:D4, COUNTIF returns 1, so the IF formula displays “In list.” If Pear occurs twice in the range, COUNTIF returns 2, but the result is still “In list.” If you need to know how many times a value occurs, use COUNTIF without the greater-than-zero test.

COUNTIF is widely compatible, including older Excel versions, and is usually the simplest choice for a basic membership test. It is not case-sensitive: Pear and PEAR are treated as a match. It also interprets asterisks and question marks in criteria as wildcard characters, which can matter when those characters are part of the actual data.

Use MATCH or XMATCH to find a match

MATCH is another way to check whether a value appears in a list. With A2 as the value and D2:D10 as the list, this formula returns “In list” if MATCH finds an exact match and “Not in list” otherwise:

=IF(ISNUMBER(MATCH(A2,$D$2:$D$10,0)),"In list","Not in list")

The final 0 tells MATCH to look for an exact match. MATCH returns a position number when it finds one and an error when it does not; ISNUMBER distinguishes the position number from the error. For instance, if Pear is the second item in D2:D10, MATCH returns 2 and the formula reports “In list.”

In Microsoft 365 or Excel 2021, XMATCH provides a newer alternative. With the same A2 and D2:D10 layout, use:

=IF(ISNUMBER(XMATCH(A2,$D$2:$D$10,0)),"In list","Not in list")

The 0 specifies exact matching. For this simple task, COUNTIF is often easier to read, while MATCH or XMATCH can be handy when you also want the position of a found item or plan to build a larger lookup formula. XMATCH is available in Microsoft 365 and Excel 2021; use MATCH or COUNTIF if a workbook must work in older Excel versions.

Choose the kind of match you need

Before choosing a formula, decide what “in the list” means for your data. Excel’s common membership formulas are not all sensitive to the same distinctions:

  • Case-insensitive match: COUNTIF and the usual MATCH and XMATCH exact-match patterns do not distinguish uppercase from lowercase. This is suitable for most names, categories, and codes where capitalization is only a display choice.
  • Case-sensitive match: If Pear and PEAR must count as different values, use EXACT inside SUMPRODUCT. With the value in A2 and the list in D2:D10, =SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0 returns TRUE when an entry matches A2 with the same capitalization. For A2 containing Pear, it returns TRUE if Pear is in the list but not if the only entry is PEAR.
  • Partial match: A membership check normally asks whether the whole cell value matches. If you instead need to search for part of a text string, that is a different task and requires a wildcard criterion or a text-search formula. Be clear about whether a partial match is acceptable before using one.

COUNTIF recognizes * as a wildcard for any number of characters and ? as a wildcard for one character in its criterion. For example, a criterion containing Jo* can match text beginning with Jo, rather than only the literal characters Jo*. That behavior is useful for intentionally searching patterns, but can cause surprising results for product codes or text that contains wildcard symbols. For a literal, case-sensitive comparison, the EXACT formula above avoids treating those characters as wildcard instructions.

Check many values and highlight matches

To check a column of values, put the formula beside the first value and copy it down. Assume the values to test are in A2:A100 and the reference list is in D2:D10. In B2, enter =IF(COUNTIF($D$2:$D$10,A2)>0,"In list","Not in list"), then fill it down through B100. The list reference stays at D2:D10, while the checked reference changes from A2 to A3, A4, and so on. Each result therefore checks the corresponding row against the same list.

You can also color matching entries without adding a helper column. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Assuming the selection starts at A2 and the lookup list is D2:D10, enter =COUNTIF($D$2:$D$10,A2)>0, choose a format, and confirm the rule. The A2 reference is relative so the rule evaluates each selected row; the dollar signs keep the comparison list fixed. Excel for Mac has Conditional Formatting tools as well, though the ribbon layout can vary by version. The formulas themselves work in Excel for Microsoft 365 on Windows and Mac.

Common problems and how to fix them

A formula can report no match even when two cells look alike. Check for leading or trailing spaces, nonprinting characters, or different underlying data types. A number stored as text can behave differently from a numeric value, and imported data may contain invisible characters. Compare the source cells in the formula bar, check the number format, and clean or standardize both the lookup value and list if needed. Changing only the display format does not necessarily change the underlying value.

Also check that the formula points to the intended range. A reference such as D2:D10 excludes values below row 10; use a longer range or a well-managed Excel Table if the list will grow. Absolute references such as $D$2:$D$10 prevent the range from shifting when you copy a formula, but they do not automatically expand to include newly added entries.

If the result is unexpected, confirm whether duplicates matter, whether capitalization should count, and whether wildcard symbols are present. A basic COUNTIF membership test answers only whether at least one criterion match exists. It does not verify that the list is unique, establish that the match is valid in a business sense, or identify which matching cell caused the result.

Which formula should you use?

For most spreadsheets, start with COUNTIF: it is compact, works in older Excel versions, and clearly expresses the task of checking a value against a range. Use MATCH when you want a position-based lookup pattern or need compatibility with older workbooks that already use it. Choose XMATCH in Microsoft 365 or Excel 2021 if you prefer its newer lookup behavior or are building on it for other lookup tasks. Use EXACT with SUMPRODUCT when capitalization must be meaningful.

For a very large list, keep the range focused on the data rather than whole columns if calculation speed becomes a concern, especially when many thousands of formulas repeat the same check. An Excel Table can make list management easier because its range can expand as rows are added. Whichever approach you choose, label the input and reference list clearly so someone reviewing the workbook can tell what the test is meant to include.

Frequently Asked Questions

How do I return Yes or No if a value is in a list? With the value in A2 and the list in D2:D10, use =IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No"). It returns Yes when the value appears at least once and No when it does not.

Can I check a list on another worksheet? Yes. If the value is in A2 on the current sheet and the list is in A2:A20 on a sheet named Lists, use =COUNTIF(Lists!$A$2:$A$20,A2)>0. If a sheet name contains spaces, enclose it in single quotes, as in ‘Product Lists’!A2:A20.

Does COUNTIF distinguish upper- and lowercase letters? No. COUNTIF treats capitalization as equivalent. Use =SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0 when A2 must match an entry in D2:D10 with the same capitalization.

Why does COUNTIF find a match I did not expect? The criterion may contain * or ?, which COUNTIF treats as wildcards, or the range may include a similar value you overlooked. COUNTIF is also not case-sensitive. Inspect the list and choose a case-sensitive comparison if capitalization or literal wildcard characters matter.

How do I return the matching item instead of just TRUE or FALSE? A membership test reports whether a match exists; it does not return the found cell’s contents. Use a lookup formula such as XLOOKUP when you need to return related information. XLOOKUP is available in Microsoft 365 and Excel 2021; older Excel versions can use alternatives such as INDEX and MATCH.

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.