Excel guide · 4 min read

Check if a value exists in a list in Excel

Q: How do I check if a value is in a column in Excel?

Use =COUNTIF(column,value)>0, for example =COUNTIF($A$2:$A$7,E2)>0. It returns TRUE when the value appears at least once.

Q: How do I return Yes or No if a value exists in a list?

Wrap the check in IF: =IF(COUNTIF($A$2:$A$7,E2)>0,"Yes","No"). Fill it down next to the values you are checking.

Q: How do I check if a value exists in another sheet?

Point the range at the other sheet: =COUNTIF(Vendors!$A$2:$A$200,E2)>0. Click the other sheet while writing the formula and Excel adds the sheet name for you.

Quick answerUse =COUNTIF($A$2:$A$7,E2)>0. It returns TRUE if the value in E2 appears anywhere in A2:A7 and FALSE if it does not. For a label, wrap it in IF: =IF(COUNTIF($A$2:$A$7,E2)>0,"Yes","No").

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	D	E	F
1	Approved vendor	Vendor ID	Invoice	Vendor	Vendor ID
2	Northwind Traders	10452	PO-7781	Calloway Print	10471
3	Brightline Office	10467	PO-7782	Harbor Signs	10533
4	Calloway Print	10471	PO-7783	Delta Fasteners	10488
5	Delta Fasteners	10488	PO-7784	northwind traders	10452
6	Evergreen Catering	10502	PO-7785	Evergreen Catering	10502
7	Fairmont Logistics	10519

Download this sheet as an Excel practice file (.xlsx)

Method 1: COUNTIF greater than 0

Accounts payable only pays vendors on the approved list in columns A and B. Each invoice in columns D to F needs a check. COUNTIF counts how many times E2 appears in the list; anything above zero means it is there.

Lock the list with $ signs so it stays put when you fill the formula down the invoices. Without them, the list slides down one row with every row you fill.

=IF(COUNTIF($A$2:$A$7,E3)>0,"Yes","No")

Result: No

No: Harbor Signs on PO-7782 is not an approved vendor. The same formula for E2 (Calloway Print) returns Yes.

Method 2: ISNUMBER(MATCH())

MATCH returns the position of the value in the list, or #N/A when it is missing. ISNUMBER turns a position into TRUE and the error into FALSE. It gives the same answer as COUNTIF for ordinary text and stops at the first match it finds.

Both methods ignore case. "northwind traders" on PO-7784 matches "Northwind Traders". If case must match, see the case-sensitive lookup guide.

=ISNUMBER(MATCH(E2,$A$2:$A$7,0))

Result: TRUE

TRUE: Calloway Print is in the list. The 0 at the end asks for an exact match; leave it out and MATCH guesses.

Method 3: XLOOKUP with "Not found"

Often you do not just want to know that a value exists, you want something from that row. XLOOKUP looks the value up and returns the matching item, and its fourth argument is what to show when there is no match, so you get a readable message instead of #N/A.

Here the invoice's vendor ID is checked against the ID column and the approved name comes back. XLOOKUP is in Excel 365 and 2021 and later; in older versions use =IFERROR(INDEX($A$2:$A$7,MATCH(F3,$B$2:$B$7,0)),"Not found").

=XLOOKUP(F3,$B$2:$B$7,$A$2:$A$7,"Not found")

Result: Not found

Not found: vendor ID 10533 is not on the list. For F2 (10471) it returns Calloway Print.

Why a value that is clearly there says "No": spaces

PO-7783 is from Delta Fasteners, which is on the list, yet COUNTIF, MATCH and XLOOKUP all say it is missing. The list entry in A5 has a trailing space, "Delta Fasteners ", copied in from an old spreadsheet. Excel compares every character, so the two do not match.

The real fix is to clean the list: a TRIM helper column, pasted back as values. If you cannot change the list, compare trimmed values on both sides with SUMPRODUCT.

=SUMPRODUCT(--(TRIM($A$2:$A$7)=TRIM(E4)))>0

Result: TRUE

TRUE: once the spaces are trimmed away, Delta Fasteners is found.

Numbers stored as text: COUNTIF and MATCH disagree

The ID in F5 came from a CSV export and is stored as text ("10452"), while the list holds real numbers. MATCH and XLOOKUP see text and a number as different values, so =ISNUMBER(MATCH(F5,$B$2:$B$7,0)) returns FALSE. COUNTIF converts number-like text before comparing, so =COUNTIF($B$2:$B$7,F5)>0 returns TRUE for the same cell.

That disagreement is a useful warning sign. Fix it by converting the lookup value inside the formula with -- (or VALUE), or convert the whole column. Text IDs are usually left-aligned and often show a small green triangle.

=ISNUMBER(MATCH(--F5,$B$2:$B$7,0))

Result: TRUE

TRUE: -- turns the text "10452" into the number 10452, which MATCH finds in B2. Use it only when every lookup value is a number.

Highlight values that are not in the list

To see the problems at a glance without a helper column, use the COUNTIF test as a conditional formatting rule. The rule is written for the first cell (E2) and Excel adjusts it for the rows below.

Select E2:E6.
Go to Home > Conditional Formatting > New Rule.
Choose "Use a formula to determine which cells to format".
Enter =COUNTIF($A$2:$A$7,E2)=0 and pick a fill color.
Click OK. Harbor Signs and Delta Fasteners are highlighted (the second because of the trailing space in the list).

Common mistakes

Forgetting the $ signs on the list

Use $A$2:$A$7. A relative A2:A7 becomes A3:A8, A4:A9 and so on as you fill down, so lower rows check only part of the list.

Wildcards in the value

COUNTIF treats * and ? as wildcards, so a value containing them can match other entries. MATCH with 0 also uses wildcards for text. Use XLOOKUP, which matches exactly by default, or put ~ before the * or ? to match it literally.

Leaving out the 0 in MATCH

Without it MATCH does an approximate match on a list it assumes is sorted, and can return a position for a value that is not there. Always write MATCH(value,list,0).

Hidden spaces and text numbers

If something you can see in the list is reported missing, test =LEN() on both cells and =ISTEXT() on IDs. Clean the data rather than patching each formula.

FAQ

How do I check if a value is in a column in Excel?

Use =COUNTIF(column,value)>0, for example =COUNTIF($A$2:$A$7,E2)>0. It returns TRUE when the value appears at least once.

How do I return Yes or No if a value exists in a list?

Wrap the check in IF: =IF(COUNTIF($A$2:$A$7,E2)>0,"Yes","No"). Fill it down next to the values you are checking.

Is COUNTIF or MATCH better for checking a list?

For plain text they give the same answer, so use whichever you find easier to read. They differ on numbers stored as text: COUNTIF matches them, MATCH does not, so MATCH is stricter.

How do I check if a value exists in another sheet?

Point the range at the other sheet: =COUNTIF(Vendors!$A$2:$A$200,E2)>0. Click the other sheet while writing the formula and Excel adds the sheet name for you.

Functions used

Related guides

Updated November 2, 2026