Yes. To show whether a value in one cell appears anywhere in another list, use COUNTIF inside IF. With the value to check in A2 and the comparison list in D2:D10, enter =IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No"). It returns Yes when A2 is found and No when it is not.
What the formula checks
The formula compares one value against a range and turns the result into a clear Yes or No. COUNTIF counts cells that meet a condition. In this case, the condition is the value in A2. IF then tests whether that count is greater than zero: a positive count means there is at least one match, while zero means there is none.
The dollar signs in $D$2:$D$10 keep the comparison range fixed when you copy the formula down a column. A2 is intentionally relative, so it changes to A3, A4, and so on for each row. This is useful when you have a main list to validate against a second list, such as checking order IDs against a shipping list or names against an approved roster.
For example, suppose D2:D10 contains the permitted region names, A2 contains West, and West appears in D5. The formula returns Yes. If A3 contains East and East does not appear in D2:D10, the same formula copied to the next row returns No. The result is a text label, not a logical TRUE or FALSE value.
Set up the lists in Excel
On Microsoft Excel for Microsoft 365 on Windows, put the values you want to test in one column and the reference list in another. Add a heading above each list if helpful, but exclude the headings from the formula range. For instance, use A2:A100 for incoming values and D2:D50 for approved values. Enter the formula in B2, then fill it down beside the values you are checking.
- Click the first result cell, such as B2.
- Type the formula using the actual cells in your workbook.
- Press Enter and confirm the first result is sensible.
- Use the fill handle at the lower-right corner of B2 to copy the formula down, or select the formula cell and the cells beneath it and press Ctrl+D on Windows. On Mac, the equivalent fill-down shortcut is Command+D.
Use a range that covers all likely entries in the reference list. If the list will grow, an Excel Table can make formulas easier to maintain because its columns expand as you add rows. The formula still needs to refer to the correct table column; ordinary cell references are often the simplest starting point.
Return Yes or No for each row
The main formula is designed for row-by-row checking: the value in A2 is tested, then the formula can be copied down to test A3 and the following cells. The reference list stays anchored because it has absolute references, while the checked value moves with each row. This layout makes it easy to filter the results column to show only No entries that may need review.
Suppose A2:A4 contains West, East, and North, while D2:D4 contains West, North, and South. With the original formula entered in B2 and filled through B4, B2 returns Yes, B3 returns No, and B4 returns Yes. Those results follow directly from whether each complete cell value appears in the D range.
If you only want one answer for the entire list—whether any value from A2:A100 appears in D2:D50—the row-by-row formula is not the same task. It checks each A cell separately. In many workbooks, checking each row and then filtering is more useful because it identifies which values matched. If the goal is merely to know whether the two lists overlap at all, use a separate approach or test the values individually rather than assuming one row’s result summarizes the whole list.
Check whether the other list contains Yes or No
There is a related but different question: does a list contain the literal word Yes or the literal word No? In that case, you are not testing whether A2 appears in the list. You are searching the reference range for either of those two words. With the possible responses in D2:D10, this formula returns a message if at least one of them is present:
=IF(OR(COUNTIF($D$2:$D$10,"Yes")>0,COUNTIF($D$2:$D$10,"No")>0),"Found Yes or No","Neither found")
For instance, if D2:D10 includes Yes but no No, the result is Found Yes or No. If it contains neither word, the result is Neither found. This checks for either answer, not whether both answers are present. If you need to test a single cell for whether it says exactly Yes or No, use a direct check rather than a list-membership formula.
Exact matches, capitalization, and spaces
COUNTIF is generally case-insensitive, so West and WEST are treated as the same text for this check. It tests whether the cell meets the criterion, but it does not distinguish capitalization. For most lists of names, codes, or yes/no responses, that behavior is appropriate.
COUNTIF also recognizes wildcard characters in criteria: an asterisk can stand for any sequence of characters, and a question mark can stand for one character. That matters if a value being checked contains those symbols, because the criterion may behave like a pattern rather than a literal string. If your IDs can include asterisks or question marks, consider an exact-comparison method instead.
Leading or trailing spaces can cause an apparent mismatch. A value that looks like West may actually contain a space after the word, so it will not match the clean entry as expected. Check the source cells by editing them or use Excel’s TRIM function in a helper column to clean imported text. Also check for nonbreaking spaces from copied web data; ordinary TRIM may not remove every unusual whitespace character. Avoid changing the source list until you know whether those spaces are meaningful.
When capitalization itself matters, use EXACT for a case-sensitive comparison. With the value in A2 and the reference entries in D2:D10, enter this formula to return Yes only if an identical, case-sensitive value is present:
=IF(SUMPRODUCT(--EXACT(A2,$D$2:$D$10))>0,"Yes","No")
If A2 contains West and the reference range contains only WEST, this version returns No. EXACT compares text case-sensitively, and SUMPRODUCT counts the TRUE results. It is a more specialized option; use the simpler COUNTIF formula unless case differences have real meaning in your data.
Other formula options
MATCH is another way to test membership, and its third argument set to 0 requests an exact match. With the value in A2 and the list in D2:D10, this formula also returns Yes or No:
=IF(ISNUMBER(MATCH(A2,$D$2:$D$10,0)),"Yes","No")
MATCH returns a position when it finds the value and an error when it does not. ISNUMBER recognizes the position, and IF supplies the readable label. COUNTIF is often easier to understand for this simple task; MATCH can be handy when you later want the position of the match or plan to build a lookup around it.
Excel 365 also includes XLOOKUP, but it is not necessary just to answer whether a value exists. The COUNTIF approach is concise and widely familiar. Choose based on what the workbook needs: a yes/no flag, a match position, or a returned value are different outcomes and may call for different functions.
Common problems and how to fix them
- Every row says No: Confirm the formula points at the correct reference column and includes all of the data rows. Check for leading spaces, trailing spaces, or values stored differently, such as a number in one list and text in the other.
- The result changes unexpectedly when copied: Keep dollar signs around the reference range, as in $D$2:$D$10. Without them, Excel may shift the range as the formula is filled down.
- A blank appears as a match: If the checked cell is blank and the reference range contains blank cells, COUNTIF may find a blank. If blank inputs should remain blank instead of showing Yes or No, add an outer IF. With values in A2 and the lookup list in D2:D10, use
=IF(A2="","",IF(COUNTIF($D$2:$D$10,A2)>0,"Yes","No")). For an empty A2, it returns an empty-looking result; otherwise it performs the membership check. - The formula displays as text: The result cell may be formatted as Text, or the entry may have a leading apostrophe. Set the cell format to General, then re-enter the formula.
- Excel reports a formula problem: Make sure the parentheses and quotation marks are balanced and that the formula begins with an equals sign. The formulas here use commas between arguments, as used in the standard English-language Excel setup. Some regional settings use semicolons as separators; if Excel rejects commas, use the separator configured for your installation.
Frequently Asked Questions
Can Excel return Yes if a value is anywhere in another column?
Yes. Use the COUNTIF and IF formula from the opening example, replacing A2 and the D2:D10 range with your own checked cell and list. It returns Yes for one or more matches and No when there are none.
Does COUNTIF check the whole cell or part of it?
With an ordinary value such as West in A2, it checks for a matching cell value. However, wildcard characters in the criterion can make the comparison act like a pattern. If special characters are part of your data, use a case-sensitive EXACT-based formula or another method designed for literal matching.
How do I make the formula return blank for an empty input?
Use the blank-safe formula in the troubleshooting section. It checks whether A2 is empty first, and only performs the list comparison when A2 contains a value.
Can I use this to compare two lists and find missing items?
Yes. Put one list beside a result column, test each item against the other list, and filter the result for No. To find items missing in both directions, repeat the check with the lists reversed.
Will the formula update when I add entries to the other list?
It updates when new entries are added inside the range referenced by the formula. If you add items below the range’s last row, expand the range or use an Excel Table and refer to its relevant column so additions are included.
Related
- How to Share Excel in Real Time and Co-Edit Safely
- How to Share an Excel Workbook in Windows 11 Safely
- How to Share Excel in Google Drive: A Clear Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIFS in Excel: How to Count OR CriteriaCOUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply.…
- Excel Date Minus 1 Month? Formula and Date RulesTo subtract one calendar month from an Excel date, use =EDATE(A1,-1), where A1 contains a real Excel date.…
- Excel INDEX Function: Chinese Data, Syntax, and ExamplesIn Excel, INDEX returns a value from a specified position in a range, and it works with Chinese…
- Excel ROUND DOWN to Whole Number: Formulas and ExamplesTo round a number down to a whole number in Excel, use ROUNDDOWN with zero decimal places. If…
- Excel Dynamic Filter Multiple Criteria: Formulas and TipsTo dynamically filter rows in Excel by multiple conditions, use the FILTER function and combine criteria with multiplication…
- Excel COUNTIF by Text Color: Options and WorkaroundsExcel’s COUNTIF function cannot count cells by font or text color: its criteria test cell values, not formatting.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.