# How to check if a value exists in a range in Excel

Count it with COUNTIF and test for more than zero: =IF(COUNTIF(A2:A9,D1)>0,"Yes","No").

**Excel:** `=IF(COUNTIF(A2:A9,D1)>0,"Yes","No")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Approved vendors |  | Vendor | Northwind Traders |
| Contoso Ltd |  | Approved? |  |
| Fabrikam Inc |  |  |  |
| Litware |  |  |  |
| Northwind Traders |  |  |  |
| Adatum Corp |  |  |  |
| Proseware |  |  |  |
| Tailspin Toys |  |  |  |
| Wingtip Supplies |  |  |  |

Formula in D2: `=IF(COUNTIF(A2:A9,D1)>0,"Yes","No")`

Result: Yes

## How it works

COUNTIF counts how many cells in the list equal the vendor name: 0 when it's missing, 1 or more when it's there. `>0` turns the count into TRUE or FALSE for IF. The match is on the whole cell, so "Northwind" alone would not count.

## Other ways

- `=IF(ISNUMBER(MATCH(D1,A2:A9,0)),"Yes","No")`: MATCH returns a position or #N/A; ISNUMBER turns that into TRUE or FALSE. Same answer, and MATCH can then tell you where the value is.
- `=COUNTIF(A2:A9,D1)>0`: Returns TRUE or FALSE directly, useful in conditional formatting and filters. (Shows TRUE instead of Yes.)

## Common mistakes

- For a partial match, add wildcards: `COUNTIF(A2:A9,"*"&D1&"*")`.
- A trailing space in the list or the input makes an exact match fail. TRIM imported lists.
- COUNTIF ignores case. For a case-sensitive check use `SUMPRODUCT(--EXACT(A2:A9,D1))>0`.

## Questions

**How do I check a whole column of values against the list?**

Use the same formula in a helper column with the list locked: `=IF(COUNTIF($A$2:$A$9,F2)>0,"Yes","No")`.

**How do I check if a value exists in either of two columns?**

Add the counts: `=IF(COUNTIF(A2:A9,D1)+COUNTIF(B2:B9,D1)>0,"Yes","No")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-value-exists-in-a-range