Google Sheets: Check Whether a Value Is in a Range

Yes. In Google Sheets, check whether a value appears anywhere in a range with =COUNTIF(A2:A20,D2)>0. It returns TRUE if the value in D2 occurs in A2:A20 and FALSE if it does not; use a different range or lookup cell to fit your sheet.

What “any of a range” can mean

The phrase can describe a few related spreadsheet questions. You might want to know whether one lookup value occurs in a column, whether at least one item from a separate list occurs there, or whether any cell satisfies a condition such as being greater than 100. These sound similar, but the most suitable formula depends on which question you mean.

The basic pattern is to count matching cells and test whether the count is above zero. That gives a clear TRUE or FALSE result that you can use in another formula, a filter helper column, or conditional formatting. If instead you need the matching value itself, you will need a lookup formula rather than only a yes-or-no test.

Check one value against a range

Suppose product codes are in A2:A20 and the code you want to find is in D2. The formula shown in the opening paragraph counts how many cells match D2, then checks whether that count is greater than zero. A count of one or more produces TRUE; a count of zero produces FALSE. It does not matter whether the code appears once or several times when all you need is an existence check.

To adapt it, replace A2:A20 with the cells to search and D2 with the cell containing your search value. For example, if email addresses are in C2:C500 and the address to check is in F2, the same pattern is =COUNTIF(C2:C500,F2)>0. Keep the searched range and lookup cell separate in the formula so the intent remains easy to read.

COUNTIF is a standard Google Sheets function. In a browser, enter the formula in an empty cell and press Enter. If your spreadsheet locale uses semicolons rather than commas to separate function arguments, Sheets may require semicolons in formulas; the examples here use commas.

How COUNTIF works

COUNTIF takes a range and a criterion. It examines the cells in the range, counts those that meet the criterion, and returns a number. The comparison with zero converts that number into a logical result. This is often easier to understand than looking for an error from a lookup function when no match exists.

For a condition rather than an exact lookup, put the condition in the criterion. If numbers are in B2:B20 and you want to know whether any is greater than 100, use =COUNTIF(B2:B20,">100")>0. The text criterion ">100" tells COUNTIF to test each cell numerically. Similar criteria include "<0" or "<>Complete" for values not equal to Complete.

COUNTIF comparisons for text are not case-sensitive, so it generally treats “North” and “north” as the same for this purpose. If case matters, choose a case-sensitive approach instead of assuming COUNTIF distinguishes capitalization.

Check whether any item from a list appears

Another common interpretation of “any of a range” is checking whether a search range contains at least one item from a separate list. Suppose the cells to search are A2:A20 and the list of acceptable items is D2:D5. In Google Sheets, use =SUM(COUNTIF(A2:A20,D2:D5))>0. The criteria range supplies the candidate values; SUM adds the match counts, and the final comparison asks whether the total is positive.

This answers “does at least one candidate occur?” It does not tell you which candidate matched or where it appeared. If a candidate is repeated in D2:D5, its matches can be counted more than once, but that does not change the TRUE-or-FALSE answer. For a simple yes-or-no check, that duplication is harmless.

For a short list of known text values, you can also write separate tests. With statuses in A2:A20, this checks whether any cell is Open or Pending: =OR(COUNTIF(A2:A20,"Open")>0,COUNTIF(A2:A20,"Pending")>0). This version is explicit and easy to edit for just a few options. If the list is long or maintained by other people, keeping the candidates in cells and referring to that list is usually more convenient.

Check whether any cell is nonblank

If you are not looking for a particular value and simply want to know whether the range contains an entry, use COUNTA. With the cells of interest in A2:A20, enter =COUNTA(A2:A20)>0. COUNTA counts cells that contain values, including text and numbers, then the comparison returns TRUE if the count is positive.

There is an important detail: COUNTA also counts cells containing formulas, even when those formulas display an empty string. Thus, a range that looks blank may still make this test TRUE if it contains formulas returning an empty result. If you need to distinguish visually displayed values from formula cells, define what should count as “not blank” and choose a test suited to that requirement.

Use the result in a sheet or conditional formatting

A TRUE/FALSE result can drive another calculation. For example, with the lookup value in D2 and the search range in A2:A20, this returns a label instead of a Boolean: =IF(COUNTIF(A2:A20,D2)>0,"Found","Not found"). You can replace those labels with messages that suit a validation column or a simple status report.

To highlight cells in A2:A20 that occur in a candidate list in D2:D5, select A2:A20 and choose Format > Conditional formatting. In the conditional-formatting panel, choose “Custom formula is” and enter =COUNTIF($D$2:$D$5,A2)>0, then choose a formatting style. The dollar signs keep the candidate list fixed as the rule evaluates each row; A2 changes to A3, A4, and so on. The rule checks each selected cell against the list.

In Excel for Windows, COUNTIF and the basic existence test are also available, but conditional formatting is configured through Excel’s Home > Conditional Formatting menus rather than Google Sheets’ Format menu. The instructions above describe Sheets in a web browser.

Alternatives and common problems

MATCH is useful when you want to test for an exact match with an explicit match mode. With the lookup value in D2 and the search range in A2:A20, use =ISNUMBER(MATCH(D2,A2:A20,0)). The final 0 requests an exact match, and ISNUMBER turns MATCH’s numeric position into TRUE or FALSE. COUNTIF is often more straightforward when you only need a count or need to use criteria such as “greater than 100.”

Unexpected FALSE: Check for extra spaces, different punctuation, or values stored as text in one place and numbers in another. A displayed number can be text, especially after importing data.
Unexpected TRUE for a blank lookup: COUNTIF can count blank cells when the criterion refers to an empty cell. If D2 may be empty, guard the check with =IF(D2="",FALSE,COUNTIF(A2:A20,D2)>0).
Wildcards matching more than expected: In a COUNTIF text criterion, an asterisk (*) can stand for any sequence of characters and a question mark (?) for one character. Use these only when partial matching is intended.
Formula parse error: Confirm the parentheses and separators, and check the spreadsheet’s locale. Some locales use semicolons between arguments instead of commas.
Only one match needed: COUNTIF counts every match before reducing the count to TRUE or FALSE. For ordinary sheet sizes, this is clear and practical; if your sheet is very large, keep ranges limited to the data rather than whole columns when appropriate.

Be especially careful when using wildcards with values that include * or ?. Those characters have special meaning in criteria; a wildcard search may report a match even when you intended to compare a literal character. For routine exact matching, the direct cell-reference criterion such as D2 avoids accidentally asking for a partial match.

Frequently Asked Questions

How do I check if a value is anywhere in a Google Sheets column?
Use =COUNTIF(A:A,D2)>0 if the column is A and D2 contains the value. For a large sheet, a bounded range such as A2:A500 may be more efficient and avoids including header cells.

How do I check if a cell matches one of several values?
For a short list, combine COUNTIF tests with OR. For a list stored in cells, use the multi-candidate COUNTIF pattern and SUM, such as =SUM(COUNTIF(A2:A20,D2:D5))>0. Both return TRUE if at least one candidate matches.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive for text comparisons. If capitalization is meaningful in your data, use a case-sensitive comparison method rather than relying on COUNTIF.

How can I find out how many matches there are, not just whether one exists?
Remove the final comparison with zero. With the search values in A2:A20 and the lookup value in D2, =COUNTIF(A2:A20,D2) returns the number of matching cells.

Can I highlight matching values instead of showing TRUE or FALSE?
Yes. Select the cells to format, open Format > Conditional formatting, and set a custom formula that checks each selected cell against the lookup value or candidate list. Choose a fill or text style to make matches visible.

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.