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

Use `=ISNUMBER(MATCH(D1,A2:A9,0))`: MATCH finds a position if the value is there, and ISNUMBER turns that into TRUE or FALSE.

**Excel:** `=ISNUMBER(MATCH(D1,A2:A9,0))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Approved vendor |  | Vendor | Bluewater Supply |
| Acme Industrial |  | Approved? |  |
| Bluewater Supply |  |  |  |
| Cedar Logistics |  |  |  |
| Delta Packaging |  |  |  |
| Evergreen Paper |  |  |  |
| Foxhall Electric |  |  |  |
| Granite Tools |  |  |  |
| Harbor Freightways |  |  |  |

Formula in D2: `=ISNUMBER(MATCH(D1,A2:A9,0))`

Result: TRUE

## How it works

MATCH returns the vendor's position, 2, when it is in the list and #N/A when it isn't. ISNUMBER is TRUE for a position and FALSE for the error, so the formula always gives a clean TRUE or FALSE.

## Other ways

- `=COUNTIF(A2:A9,D1)>0`: Counts the vendor in the list. Also works across a block of several columns.
- `=IF(COUNTIF(A2:A9,D1),"Approved","Not approved")`: Returns a label instead of TRUE or FALSE. (Shows "Approved".)

## Common mistakes

- Both tests ignore case, and a trailing space makes "Bluewater Supply " a different value.
- COUNTIF treats * and ? as wildcards, so a value containing them can be counted wrongly. MATCH with 0 does too; EXACT doesn't.

## Questions

**How do I check for part of the text?**

Use wildcards: `=COUNTIF(A2:A9,"*"&D1&"*")>0`.

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

Fill `=ISNUMBER(MATCH(D2,$A$2:$A$9,0))` down, or in Excel 2021 or Microsoft 365 use `=ISNUMBER(MATCH(D2:D20,A2:A9,0))`.

**How do I do a case-sensitive check?**

Use EXACT: `=SUMPRODUCT(--EXACT(A2:A9,D1))>0`.

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-list