Google Sheets: Check If a Value Is in a List

In Google Sheets, check whether a value appears in a list with =COUNTIF($A$2:$A$10,D2)>0, assuming the list is in A2:A10 and the value to check is in D2. The formula returns TRUE if Sheets finds a match and FALSE if it does not.

What the list-membership check does

A list-membership test answers a simple question: does this value occur anywhere in a specified range? It is useful when comparing an order against approved product codes, checking whether a name belongs to a roster, or flagging entries that are not in an accepted set. The test produces a Boolean result, TRUE or FALSE, which you can use directly or turn into a more readable label.

For example, suppose A2:A6 contains apple, pear, mango, plum, and lime, while D2 contains pear. With that layout, =COUNTIF($A$2:$A$6,D2)>0 returns TRUE: COUNTIF finds one occurrence of pear, and one is greater than zero. If D2 contains peach, the count is zero and the result is FALSE.

The dollar signs in $A$2:$A$10 lock the list range when you copy the formula down. D2 remains relative, so the next row checks D3 against the same list. If the formula is only for one cell and will not be copied, the dollar signs are optional.

How COUNTIF works

COUNTIF counts cells in a range that meet a criterion. The formula compares that count with zero, so it does not matter whether a match appears once or many times. A list with duplicate entries still produces TRUE when at least one matching entry is present.

To display words instead of TRUE or FALSE, use this layout: the allowed list is A2:A10 and the value to check is D2. The formula is =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). It displays Found for pear and Not found for peach in the example above. You can replace those labels with text that fits your sheet, such as Valid and Review.

COUNTIF is a practical default because it is short, easy to read, and available in Google Sheets. It checks a range rather than requiring you to compare the value with each list item separately. For a long list, keep the range focused on the actual list where possible; referencing an entire column can be convenient, but it may make a busy spreadsheet harder to maintain.

Other formulas for checking membership

MATCH is another common option. It looks for a value in a range and returns its position when it finds one. ISNUMBER then converts that position into TRUE, or converts the no-match result into FALSE. With the list in A2:A10 and the value in D2, use =ISNUMBER(MATCH(D2,$A$2:$A$10,0)). The final zero asks MATCH for an exact match rather than an approximate one.

COUNTIF is often clearer when the only question is whether a value exists. MATCH is helpful when you may also want the position of the match for another calculation. For example, once a match is found, you could use a lookup formula to return a related value from another column. The membership test itself does not return the matching row’s other information.

For a case-sensitive comparison, use EXACT with SUMPRODUCT. With the list in A2:A10 and the value in D2, the formula is =SUMPRODUCT(--EXACT($A$2:$A$10,D2))>0. EXACT distinguishes values such as ABC and Abc; SUMPRODUCT adds the TRUE/FALSE comparisons, and the final test checks whether that total is above zero. This is more specialized than COUNTIF and is useful only when capitalization is meaningful in your data.

Checking many values at once

If you need to check a column of entries, place the membership formula beside the first entry and fill it down. For example, with approved codes in A2:A100 and submitted codes in D2:D50, enter =COUNTIF($A$2:$A$100,D2)>0 in E2, then copy it through E50. Each row tests the value in column D against the fixed approved list. TRUE values are matches; FALSE values need attention.

You can also use the result as a filter or summary. Turn on a filter with Data > Create a filter, then filter the result column to show only FALSE rows for review. To count how many submitted values are not on the list, count the FALSE results in the membership column. This separates the task of identifying exceptions from the task of deciding what to do with them.

Consider adding a header such as On approved list? rather than leaving the result column unexplained. That small label makes the TRUE and FALSE values easier for other people to interpret, especially if the sheet is shared or the formula is part of a larger workflow.

Highlight matches with conditional formatting

You do not have to add a separate TRUE/FALSE column if you only want visual highlighting. To highlight submitted values in B2:B100 when they appear in a reference list in A2:A50, select B2:B100, then choose Format > Conditional formatting. In the sidebar, set the rule to Custom formula is and enter =COUNTIF($A$2:$A$50,B2)>0. Choose a formatting style and select Done.

The first range is fixed so every cell checks the same reference list. B2 is intentionally not fixed: when the rule applies to B3, Sheets evaluates the corresponding cell in that row. If you want to highlight values that are missing instead, change the final test to =COUNTIF($A$2:$A$50,B2)=0. Check the Apply to range setting if the rule appears to highlight the wrong rows.

Conditional formatting changes how cells look; it does not change their contents or prevent invalid entries. If you need a visible status that can be filtered or counted, a helper column with a formula is usually more useful. You can use both approaches when the result needs to be obvious at a glance and available for further analysis.

Use a dropdown to prevent off-list entries

A membership formula checks values that are already present. If the goal is to guide people toward valid choices while they enter data, use data validation instead. Select the input cells and choose Data > Data validation. Add a rule that uses Dropdown (from a range), then specify the cells containing the allowed options. The exact controls appear in the data-validation sidebar.

Depending on the rule settings, Sheets can show a warning or reject an entry that is not on the list. A dropdown is useful for a short set of standard choices such as department names or order statuses. A formula-based check is better for auditing imported data, spotting old entries after a list changes, or checking values people entered before validation was set up.

Validation and membership tests can work together. A dropdown reduces accidental variations during data entry, while COUNTIF can check an existing column or confirm that values still match an updated reference list. Neither approach automatically resolves a mismatch; someone may still need to correct, approve, or investigate the flagged value.

Common problems and exact-match details

Blank values: If D2 is blank and the reference range contains blank cells, COUNTIF can treat the blank as a match. To require a nonblank value as well, with the list in A2:A10 and the value in D2, use =AND(D2<>"",COUNTIF($A$2:$A$10,D2)>0).
Capitalization: COUNTIF is not case-sensitive. If capitalization must distinguish two codes, use the EXACT and SUMPRODUCT approach instead.
Extra spaces: A value with a leading or trailing space may not match the visually similar list item. Check the source data, or clean a separate copy with TRIM before comparing. TRIM removes ordinary extra spaces but does not fix every kind of imported whitespace.
Wildcards: In COUNTIF criteria, an asterisk and question mark can act as wildcards. If values may literally contain those characters and you need literal equality, use an equality-based comparison such as SUMPRODUCT with EXACT rather than relying on COUNTIF.
Numbers stored as text: The number 123 and text that looks like 123 can behave differently in comparisons, depending on how the values were imported and formatted. If a match seems to be missing, inspect the underlying values and make their data types consistent.

Formatting alone does not always reveal the underlying issue: two cells can look identical while one contains an extra space or is stored as text. A useful troubleshooting step is to compare a suspicious value directly with a known list item in a spare cell, then clean or normalize the source data if needed.

Choosing the right method

Use COUNTIF for a straightforward yes-or-no check, MATCH when the position of a found item matters, and EXACT with SUMPRODUCT when case-sensitive matching is required. Use conditional formatting for visual scanning, and data validation when you want to restrict or guide future entries. These methods answer related but distinct questions: finding an existing value, displaying its status, or controlling what can be entered.

The basic COUNTIF and MATCH formulas are also available in Excel, although the menu steps described here are for Google Sheets in a web browser. In Sheets, conditional formatting is under Format and validation is under Data. If you move the sheet to Excel, check the workbook’s rules and formatting after import because interface details and some behaviors can differ.

Frequently Asked Questions

Can Google Sheets return Yes or No instead of TRUE or FALSE?
Yes. Wrap the COUNTIF test in IF and choose the labels you want. For example, the IF formula above returns Found or Not found rather than a Boolean result.

Does COUNTIF check for an exact match?
It checks the criterion according to COUNTIF rules, and it is case-insensitive. Wildcard characters in a criterion can have special meaning, so use an equality-based or EXACT comparison when literal character-by-character matching is important.

Can I check whether a value appears in more than one column?
Yes. You can combine the counts from the ranges or create one continuous reference range if the data layout allows it. Make sure the formula covers every relevant cell, and avoid mixing unrelated columns into the lookup range.

Why does the formula say FALSE when the values look the same?
Common causes include leading or trailing spaces, a number stored as text, or a different underlying character. Inspect the source values and make their contents and data types consistent before changing the formula.

Can I use this check for a dropdown list?
A formula can audit or label values against a list, but it does not create the dropdown itself. Set up a Dropdown (from a range) rule through Data > Data validation to offer allowed choices during entry.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.