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

Q: 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").

Formula (Excel and Google Sheets)

=IF(COUNTIF(A2:A9,D1)>0,"Yes","No")

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

Result on the example: Yes in D2
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

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.

=IF(COUNTIF(A2:A9,D1)>0,"Yes","No")

Result"Yes"

A2:A98 rows × 1 col: "Contoso Ltd", "Fabrikam Inc", "Litware", "Northwind Traders", …
A range: 8 cells in one column.
D1"Northwind Traders"
The value in D1.
COUNTIF(A2:A9,D1)1
COUNTIF: Counts cells that meet one condition.
- rangeA2:A98 rows × 1 col: "Contoso Ltd", "Fabrikam Inc", "Litware", "Northwind Traders", …
- criteriaD1"Northwind Traders"
COUNTIF(A2:A9,D1)>0TRUE
The > operator tests greater than.
IF(COUNTIF(A2:A9,D1)>0,"Yes","No")"Yes"
IF: Returns one value if a test is true, another if false.
- logical_testCOUNTIF(A2:A9,D1)>0TRUE
- value_if_true"Yes"
- value_if_false"No"not used

In Google Sheets

The same formula works in Google Sheets: =IF(COUNTIF(A2:A9,D1)>0,"Yes","No"). We ran it in Google Sheets mode on the same data and it returns Yes.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IF(ISNUMBER(MATCH(D1,A2:A9,0)),"Yes","No")	Yes	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	TRUEDifferent result	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Check if a value exists in a range

In D2, show "Yes" when the vendor in D1 appears in the approved list A2:A9, otherwise "No".

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").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.