With the value to check in A2 and the allowed list in D2:D10, enter =IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No"). It returns Yes if A2 appears in the list and No if it does not; COUNTIF is not case-sensitive.
How the COUNTIF and IF formula works
COUNTIF checks a range against a criterion and returns how many matching cells it finds. In this example, $D$2:$D$10 is the range and A2 is the criterion. The test >0 asks whether there is at least one match. IF then returns the text Yes when that test is true and No when it is false.
The dollar signs make the list range absolute. If you copy the formula down from B2 to B3, Excel changes A2 to A3 but keeps looking in D2:D10. With Pear in A2 and Pear somewhere in D2:D10, the result is Yes. If Peach is in A2 and is absent from the list, the result is No. The count itself is not displayed; IF converts it into the label you asked for.
This pattern is useful for checking product codes against an approved list, confirming whether a name is on a roster, or flagging entries that are allowed. The list can be on the same worksheet or another worksheet, provided the range reference points to the correct cells.
Set up the list and copy the result down
Put the values you want to test in one column, such as A2:A100, and the reference list in another, such as D2:D10. Enter the formula in B2, next to the first value, then fill it down beside the remaining entries. You can drag the fill handle at the lower-right corner of the selected cell, or copy and paste the formula. The relative reference to A2 will adjust for each row.
For a list on a worksheet named Lists, with the lookup value in A2 on the current worksheet and the allowed values in Lists!A2:A10, use this formula in B2:
=IF(COUNTIF(Lists!$A$2:$A$10,A2)>0,"Yes","No")
The result is Yes if A2 occurs in Lists!A2:A10; otherwise it is No. If the worksheet name contains spaces, put single quotation marks around the name, as in ‘Approved Items’!$A$2:$A$10. Make sure the lookup value and the list are the kinds of values you expect to compare: for example, codes stored as text should ideally be stored as text throughout.
What counts as a match?
COUNTIF ignores capitalization when it compares text. If the list contains north, an entry of NORTH in A2 counts as a match. For many checklists, that is the expected behavior. COUNTIF also treats the asterisk (*) and question mark (?) as wildcard characters in its criteria: * can stand for any sequence of characters, and ? can stand for one character. That can produce a surprising Yes if the value being checked contains one of those characters.
If the comparison must be case-sensitive and treat characters literally, use EXACT inside SUMPRODUCT instead. With the value in A2 and the comparison list in D2:D10, enter this formula in B2:
=IF(SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0,"Yes","No")
EXACT compares text with case sensitivity, so Pear and PEAR are different. It also avoids COUNTIF’s wildcard-criteria behavior. For example, if A2 is Pear and the list contains only PEAR, this formula returns No. This stricter method is usually unnecessary for ordinary names or labels, but matters for case-sensitive identifiers or values containing wildcard characters.
Handle blank cells deliberately
A blank lookup cell can be an important edge case. If the reference list includes blank cells, COUNTIF may count a blank criterion as a match, so the formula can return Yes even though A2 has no visible value. Decide whether an empty input should be treated as No, left blank, or handled in some other way instead of relying on the list’s contents to decide.
To leave the result empty when A2 is empty, and otherwise return Yes or No, use this formula in B2 with A2 as the input and D2:D10 as the list:
=IF(A2="","",IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No"))
When A2 is empty, the result is an empty string, which appears as a blank cell. When A2 contains Pear and Pear is in the list, it returns Yes. If you prefer an explicit No for an empty input, omit the outer IF and use the basic formula. Be aware that a cell containing a formula that returns an empty string is not truly empty, even though it looks blank.
Use a growing or named list
A fixed range such as D2:D10 is simple, but it will not automatically include new entries typed below row 10. If the approved list grows, either update the range or put the list in an Excel Table. To create one, select the list and choose Insert > Table, then confirm whether the table has headers. Tables expand as you add rows, which can make a reference list easier to maintain.
You can also define a name for a range using Formulas > Define Name, then use that name in place of the cell range. For instance, if the name is AllowedItems and the value to check is in A2, use this formula in B2:
=IF(COUNTIF(AllowedItems,A2)>0,"Yes","No")
That formula returns Yes when A2 is found in the named range. A descriptive name can make a long formula easier to read, but someone maintaining the workbook must keep the named range pointed at the complete list. A table or a carefully maintained named range is safer than assuming a fixed range will expand on its own.
Alternatives when you need more than Yes or No
If you need the matching position rather than a label, use XMATCH. With the lookup value in A2 and the list in D2:D10, enter this in B2:
=XMATCH(A2,$D$2:$D$10)
For Pear in A2, if Pear is the fourth item within D2:D10, the result is 4. If there is no match, XMATCH returns an #N/A error, so it is less convenient than IF and COUNTIF when the desired output is simply Yes or No. XMATCH is available in Microsoft 365 and Excel 2021 or later; COUNTIF works in older Excel versions too.
To return a value associated with a match, rather than a yes/no test, use a lookup function such as XLOOKUP. For example, a product list may have product codes in D2:D10 and descriptions in E2:E10. With the code to search for in A2, a formula can return the corresponding description, or a message when there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Choose it when you need the matching record; choose COUNTIF when you only need to test membership.
Common reasons a match appears to fail
If a value that looks identical returns No, check the underlying cell contents as well as what is displayed. One cell may contain a trailing space, a nonbreaking space copied from a web page, or a different character that resembles the expected one. COUNTIF does not remove unwanted spaces automatically. You can clean data in a helper column with TRIM for ordinary leading, trailing, and repeated spaces, or correct the source values. TRIM does not remove every special whitespace character, so pasted data may need additional cleanup.
Also check whether one value is stored as text and the other as a number. A code such as 00123 is often intentionally text because its leading zeros matter; converting it to a number can change its displayed form and affect comparisons. Keep both sides in a consistent format, and do not convert identifiers to numbers if their formatting is meaningful. If you suspect the formula is looking at the wrong cells, click the formula cell and inspect the highlighted references. Confirm that the list range includes all entries and that dollar signs keep it fixed when the formula is copied.
- Check for leading or trailing spaces and pasted special characters.
- Confirm that the list range includes the value you expect to match.
- Compare the underlying values and data types, not just their displayed formatting.
- Remember that the standard COUNTIF comparison is not case-sensitive and supports wildcard criteria.
Frequently Asked Questions
How do I return Yes if a value exists in another column?
Put the value to check in one cell and the other column’s list in a range. Use IF with COUNTIF, such as the opening formula, and point COUNTIF at that other column. Lock the list range with dollar signs if you plan to copy the formula down.
Can Excel return Yes or No instead of TRUE or FALSE?
Yes. Wrap the COUNTIF test in IF and provide the text labels as its second and third arguments: Yes for a count greater than zero, and No otherwise. Put the labels in straight double quotes in the formula.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF text comparisons are case-insensitive, so Pear and PEAR count as a match. Use the EXACT and SUMPRODUCT approach above if capitalization must be significant.
Why does COUNTIF say Yes when the exact text is not in the list?
A wildcard in the criterion can match more than a literal text string, and stray spaces or inconsistent data can also cause unexpected results. If literal, case-sensitive comparison is required, use EXACT with SUMPRODUCT; otherwise inspect and clean the values and confirm the range.
Can the formula return blank instead of No when the input is empty?
Yes. Add an outer IF that checks whether the input cell is empty before running COUNTIF. The blank-handling example above returns an empty string for an empty A2 and otherwise returns Yes or No.
Related
- Excel European Number Format: Decimals and Separators
- Excel Subscript Out of Range: Causes and Fixes
- Excel Engineering Number Format Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Generate Invoice Numbers in Excel With a Simple FormulaTo generate invoice numbers in Excel, enter ="INV-"&TEXT(ROW()-1,"0000") in cell A2 when row 1 is a header and…
- Excel Power Automate Filter Query: Syntax and ExamplesIn Power Automate, filter Excel table rows by entering an OData-style condition in the Filter Query field of…
- How to Show a Unique Count in Excel with a FormulaTo count distinct nonblank values in Excel for Microsoft 365, put your data in A2:A100 and enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)…
- Excel Text Filter for More Than Two Criteria: 3 WaysExcel’s standard Text Filters menu lets you combine only two conditions in its Custom Filter dialog. To filter…
- SUMIF Criteria Examples: Text, Dates, Numbers, and MoreIn Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in…
- Filter Cells With Specific Text in Excel: A Quick GuideTo filter cells by text in Excel for Microsoft 365 on Windows, select your data, choose Data >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.