With list items in A2:A10 and the value to check in D2, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). COUNTIF checks whether the value occurs in the range, and IF displays a readable result; for a simple TRUE or FALSE result, use =COUNTIF($A$2:$A$10,D2)>0.
How the membership check works
Excel does not have a dedicated function named ISIN or INLIST. Instead, you count matches in the list and test whether the count is greater than zero. COUNTIF(range, criteria) returns how many cells meet a condition. A positive count means the value is present; zero means it is absent. The comparison with zero turns that count into a logical TRUE or FALSE.
For example, suppose A2:A6 contains Apple, pear, plum, orange, and pear, while D2 contains pear. With those values in place, =COUNTIF($A$2:$A$6,D2) returns 2 because pear appears twice. The IF version returns Found. The formula checks for at least one occurrence, so duplicate entries do not change the membership answer.
The dollar signs in $A$2:$A$10 lock the list range when you copy the formula down. D2 is left relative, so the next row checks D3 against the same list. If your lookup value is in another cell or your list has a different size, change the references to fit your sheet.
Choose a result that suits the next step
A logical result is useful when another formula will use the test. With list items in A2:A10 and the lookup value in D2, =COUNTIF($A$2:$A$10,D2)>0 returns TRUE or FALSE. You can use it inside IF, AND, OR, or other calculations without first converting it to words.
For a user-facing label, the earlier IF formula returns Found or Not found. You can replace those labels with any text that suits your workbook, such as Yes and No. If you want to return the matching list item rather than a status, use a lookup function instead; that is a different task from simply checking membership.
To avoid showing Not found when the lookup cell is empty, wrap the check in another IF. With list items in A2:A10 and the lookup value in D2, use =IF(D2="","",IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found")). It leaves the result cell blank if D2 is blank. This also treats a formula that returns an empty string as blank, which is often helpful in a prepared worksheet.
Use MATCH when you want a position or a test
MATCH is a good alternative when you may also need to know where the item appears. Set its third argument to 0 for an exact-match search. With list items in A2:A10 and the lookup value in D2, =MATCH(D2,$A$2:$A$10,0) returns the relative position of the first match in that range. If pear is the second item in A2:A10, the result is 2—not the worksheet row number.
MATCH returns #N/A when it cannot find a match. To convert its result into a clean TRUE or FALSE test, use ISNUMBER. With the same layout, =ISNUMBER(MATCH(D2,$A$2:$A$10,0)) returns TRUE if there is a match and FALSE if there is not. This approach can be convenient if a position-based lookup may follow later.
In Microsoft 365 and Excel 2021 or later, XMATCH is another option. With values in A2:A10 and the lookup value in D2, =ISNUMBER(XMATCH(D2,$A$2:$A$10)) produces a Boolean membership result. XMATCH searches for an exact match by default. COUNTIF remains a straightforward choice when all you need is a count or a basic present-or-absent answer. XLOOKUP, XMATCH, and other newer functions are not available in every older Excel release.
Understand exact matches, wildcards, and capitalization
COUNTIF is not case-sensitive: it treats Apple and APPLE as matching text. It also treats the asterisk (*) and question mark (?) as wildcard characters in its criteria. An asterisk can stand for any number of characters, and a question mark can stand for one character. That behavior is useful for pattern searches, but it can surprise you if the lookup value itself contains one of those characters.
For instance, a COUNTIF criterion containing asterisk may match more than a cell containing a literal asterisk. Excel uses a tilde to escape wildcard characters in criteria: ~* represents a literal asterisk, ~? a literal question mark, and ~~ a literal tilde. If your data includes such characters and you need literal comparison, consider a direct equality test rather than assuming COUNTIF treats every criterion as plain text.
To require matching capitalization, use EXACT, which compares text case-sensitively. With list items in A2:A10 and the lookup value in D2, =SUMPRODUCT(--EXACT($A$2:$A$10,D2))>0 returns TRUE only if at least one list entry has the same characters and capitalization. EXACT returns a set of results for the range, and SUMPRODUCT totals the matches. For ordinary membership checks where capitalization should not matter, COUNTIF is simpler.
Highlight matching values with conditional formatting
A formula can check membership without changing the appearance of the sheet. If you want matching lookup values highlighted, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that refers to the first selected cell and the fixed list range.
For example, if you select D2:D100, with the list in A2:A10, enter =COUNTIF($A$2:$A$10,D2)>0 as the conditional-formatting rule and choose a format. The range is locked, while D2 adjusts for each row in the selection. The rule highlights cells whose values occur in the list. If your selection begins in a different row, change D2 to the first cell in that selection. Conditional formatting changes appearance; it does not put a Found label in a worksheet cell.
Check common data and formula problems
A formula can report Not found even when two entries look alike. Often, one value has a leading or trailing space, a nonbreaking space copied from a webpage, or a different underlying type. For example, the number 123 and text “123” may be stored differently, and dates can be stored as serial numbers even when displayed as dates. Check the source cells’ contents and number formats before changing the membership formula.
- Compare the actual values, not only how they appear on screen. Temporarily widen columns or inspect the formula bar.
- Clean imported text when needed. TRIM removes ordinary extra spaces at the beginning and end, but it does not remove every nonprinting character or every nonbreaking space.
- Make sure the lookup cell and list use compatible data types. Converting text numbers to numbers, or vice versa, may be the right fix when the data is meant to represent the same kind of value.
- Check that the range includes all list entries. A fixed reference ending at row 10 will not automatically include a new value typed in row 11.
Errors in the lookup cell or source data can also affect a calculation. If you want to show a controlled message when the formula itself encounters an error, use IFERROR. With list items in A2:A10 and the lookup value in D2, =IFERROR(IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"),"Check data") displays Check data if an error occurs. Do not use error handling to hide data problems you need to investigate; it can make a faulty input look like a normal result.
Make the formula easier to maintain
If the list grows regularly, convert it into an Excel table: select a cell in the data and choose Insert > Table, confirming whether the range has headers. Tables expand when new rows are added, which helps prevent a membership formula from overlooking new entries. For a table named Inventory with a column named Item, and the lookup value in D2, use =COUNTIF(Inventory[Item],D2)>0. Structured references use the table and column names, so rename the references if your table uses different names.
Another option is to put the list in a dedicated named range and use that name in the formula. This can make formulas easier to read when the list is used across several sheets. Whether you choose a table, a named range, or a fixed cell range, make sure the reference covers the complete list and does not accidentally include unrelated values.
When copying formulas down a long column, keep the list reference fixed and the lookup reference relative. The dollar signs in $A$2:$A$10 do that for a standard range. After filling the formula down, check a few rows at the top and bottom to confirm that the lookup cell changed by row while the list reference stayed put.
Frequently Asked Questions
What is the simplest Excel formula to see if a value exists in a list?
With the list in A2:A10 and the value in D2, use =COUNTIF($A$2:$A$10,D2)>0. It returns TRUE when at least one match exists and FALSE otherwise.
How do I show Yes or No instead of TRUE or FALSE?
With the same cell layout, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Yes","No"). Replace the two text labels with the wording you want.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive. For a case-sensitive check, use the EXACT and SUMPRODUCT formula shown above.
How can I check whether a value is missing from the list?
Reverse the test. With values in A2:A10 and the lookup value in D2, use =COUNTIF($A$2:$A$10,D2)=0. It returns TRUE when no match is present.
Can I check a value against a list on another worksheet?
Yes. Qualify the range with the sheet name. For a list in A2:A10 on a sheet named Lists and a lookup value in D2 on the current sheet, use =COUNTIF(Lists!$A$2:$A$10,D2)>0. If the sheet name contains spaces, enclose it in single quotes, as in ‘Product Lists’!$A$2:$A$10.
Related
- Excel Date Number to Text: Convert Dates Clearly
- Excel Data Validation List: Create a Yes/No Dropdown
- Excel Data Validation Simple List: Create and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel RIGHT, LEFT, and MID Formulas: Syntax and ExamplesIn Excel, RIGHT extracts characters from the end of text, LEFT extracts them from the beginning, and MID…
- Excel Formula for Dates Before Today: Flag, Filter, FormatWith dates in A2:A100, use =A2<TODAY() to test whether the date in A2 is before today; Excel returns…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
- Remove Spaces in Excel: The Shortcut and Right MethodTo remove every ordinary space from selected cells in Excel for Microsoft 365 on Windows, select the cells,…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- How to Sort an Excel Table by One Column Without ErrorsTo sort an Excel table by one column, click the arrow in that column’s header and choose an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.