To check whether a value in Excel appears in a list, use =IF(COUNTIF($B$2:$B$20,A2)>0,"Match","No match"). With the value to check in A2 and the comparison list in B2:B20, the formula returns “Match” if the value is found and “No match” if it is not.
Excel can check for an exact value, compare two cells on the same row, find a match and return related information, or highlight matching cells visually. The best method depends on whether you need a simple yes-or-no answer, a result in another column, or a way to spot matches at a glance.
What Excel means by a match
A match is a value Excel recognizes as equivalent to another value under the rules of the method you use. That can mean the same text, the same number, or—if you specify a different search method—a value that meets a condition. For example, checking whether “North” appears in a list is different from checking whether a cell contains the word “North” somewhere inside a longer description.
Most basic Excel comparisons are not case-sensitive: “North” and “north” count as equal. A number stored as text can also behave differently from a true numeric value in some operations. And two text entries that look identical may not match if one contains an extra space or an invisible character. These details matter when a formula seems to miss an obvious match.
It helps to decide what “match” means before choosing a formula:
- Exact value in a list: Does A2 appear anywhere in B2:B20?
- Same-row comparison: Are A2 and B2 equal?
- Case-sensitive match: Are the characters identical, including capitalization?
- Partial match: Does a cell contain a word or sequence of characters?
- Lookup: If a match exists, what information belongs to it?
Check whether a value appears in a list
For a straightforward yes-or-no test, COUNTIF is a familiar option. With the value to check in A2 and the list in B2:B20, this formula returns “Match” when the count is greater than zero and “No match” otherwise: =IF(COUNTIF($B$2:$B$20,A2)>0,"Match","No match"). For example, if A2 contains “Pear” and the list contains Pear once, COUNTIF returns 1 and the formula displays “Match.” If the list contains no Pear entry, the count is 0 and the result is “No match.”
The dollar signs keep the list fixed when you copy the formula down, while A2 changes to A3, A4, and so on. Enter the formula in C2 and use the fill handle to copy it alongside a column of values. Each row will then be checked against the same B2:B20 list.
If you only need a TRUE or FALSE result, you can omit IF. With the same A2 and B2:B20 layout, =COUNTIF($B$2:$B$20,A2)>0 returns TRUE when the count is above zero and FALSE when it is zero. This is handy when another formula will use the result, or when you want a compact check rather than a label.
COUNTIF treats text comparisons as case-insensitive and supports wildcard characters. In a search criterion, an asterisk (*) can stand for any sequence of characters and a question mark (?) can stand for one character. That makes COUNTIF useful for pattern searches, but it can be surprising if the value being checked contains one of those characters and you intend it literally.
Use XMATCH for an exact list check
In Microsoft 365 and Excel 2021 or later, XMATCH can locate a value in a range. To turn that position into a simple label, with A2 as the lookup value and B2:B20 as the list, use =IF(ISNUMBER(XMATCH(A2,$B$2:$B$20,0)),"Match","No match"). The final argument, 0, requests an exact match. If Pear is the third item in the list, XMATCH returns 3, ISNUMBER is TRUE, and the formula displays “Match.” If it is absent, XMATCH returns an error, ISNUMBER is FALSE, and the formula displays “No match.”
XMATCH is useful when you may later want the position of the found value, not only a yes-or-no result. It also makes the exact-match setting explicit. For a basic membership test, however, COUNTIF is often easier to read. If your Excel version does not include XMATCH, COUNTIF is a practical alternative for ordinary list checks.
Compare two cells on the same row
When each value has a corresponding partner on the same row, compare the cells directly. With the first value in A2 and the second in B2, enter =IF(A2=B2,"Match","Different"). If both cells contain 125, the result is “Match”; if B2 contains 126, it is “Different.” Copy the formula down to check additional pairs.
Direct equality is usually the clearest choice for comparing two cells. It compares values without looking through a separate list. Like many ordinary Excel comparisons, it does not distinguish between uppercase and lowercase text. For example, A2 containing “ABC” and B2 containing “abc” will be treated as equal by this test.
To require identical capitalization, use EXACT. With the two values in A2 and B2, =IF(EXACT(A2,B2),"Match","Different") returns “Match” only when the text has the same characters and capitalization. If A2 is “ABC” and B2 is “abc,” the result is “Different.” EXACT is useful for codes or identifiers where letter case is meaningful, but it does not remove extra spaces or otherwise clean the text.
Find a match and return related information
Sometimes the real goal is not just to learn whether a value exists, but to retrieve its associated detail. For example, a product list might have product codes in B2:B20 and descriptions in C2:C20. In Microsoft 365 or Excel 2021 and later, XLOOKUP can return a description for the code in A2. With those columns as the layout, =XLOOKUP(A2,$B$2:$B$20,$C$2:$C$20,"Not found",0) returns the matching description, or “Not found” if there is no exact match. If A2 contains a code whose description is “Pear, green,” that text is returned.
The lookup range and return range must line up: the first item in B2:B20 corresponds to the first item in C2:C20. The optional fourth argument supplies a clear message when there is no result, and the final 0 specifies exact matching. If a code appears more than once, XLOOKUP returns the first match it encounters, so check for duplicate keys if each code is supposed to be unique. XLOOKUP is not available in some older Excel versions; there, lookup formulas such as VLOOKUP or INDEX with MATCH may be needed, with different syntax and limitations.
Highlight matches instead of writing labels
Conditional formatting can make matches visible without adding a results column. To highlight values in A2:A20 that also occur in B2:B20, select A2:A20, then choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the formula below, choose a format, and confirm. With A2:A20 as the selected range and the comparison list in B2:B20, use =COUNTIF($B$2:$B$20,A2)>0. If A2 is found in the list, the formula evaluates TRUE for that cell and Excel applies the chosen formatting.
The reference A2 is intentionally relative for the selected range: Excel adjusts it as the rule applies to A3, A4, and the other cells. The dollar signs keep the comparison list fixed. If you instead want to highlight duplicates within one selected column, select that column or range and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That built-in rule checks for repeated values in the selected range; it does not compare one list against a separate list.
The Windows and Mac versions of Excel for Microsoft 365 offer conditional formatting, though labels or dialog layouts can vary slightly by release. Excel for the web also supports conditional formatting, but its available options and dialogs may differ from the desktop app. If a rule does not appear to work, check that the formula refers to the top-left cell of the selected range and that the rule applies to the intended cells.
Why Excel may not find an apparent match
When a formula returns “No match” unexpectedly, inspect the source values before changing the formula. A visible match may conceal differences that are hard to notice in a worksheet.
- Leading or trailing spaces: “Pear” and “Pear ” are different text values. A space can come from manual entry or copied data.
- Numbers stored as text: The text entry “00125” may not behave like the number 125. Decide whether leading zeros are meaningful before converting values.
- Different data types: A date may be stored as text in one list and as a date value in another. Matching-looking display formats do not guarantee identical underlying values.
- Hidden characters: Imported data may contain nonprinting characters or nonbreaking spaces that are not removed by a simple visual check.
- Wildcards in COUNTIF criteria: A value containing * or ? may be interpreted as a pattern. Use another method if those characters must be treated literally.
- Wrong range or shifting references: If a formula is copied down without absolute references on the list, its lookup range may move and stop covering the intended cells.
For ordinary extra spaces, TRIM can help clean text, but it does not remove every kind of invisible character. CLEAN removes many nonprinting characters, though imported nonbreaking spaces may require separate replacement. Clean a copy of the source data first, especially if it contains important identifiers, and make sure the cleanup does not remove meaningful spaces or leading zeros.
Choose the right method
Use the simplest tool that answers the actual question. COUNTIF is a good general-purpose choice for checking membership in a list. Direct equality is best for comparing paired cells. EXACT is appropriate when capitalization matters. XMATCH is useful when you need the position of a match, and XLOOKUP is the stronger fit when the goal is to return related information. Conditional formatting is helpful when visual review matters more than a separate result column.
For a large workbook, consider converting the data ranges to Excel tables using Insert > Table. Table references can expand as new rows are added, reducing the chance that a formula misses recently appended data. Still, verify that the lookup column and return column contain the expected records and that duplicate keys are handled deliberately.
Excel’s Find command is another option for a one-time manual search: press Ctrl+F on Windows or Command+F on Mac, type a value, and search the sheet or workbook. Find is useful for locating an entry, but it does not create a reusable match result or check every row automatically. For repeatable checks, a formula or conditional formatting rule is more dependable.
Frequently Asked Questions
How do I check if two columns match in Excel?
For corresponding values on the same row, compare the cells with =A2=B2. Copy the formula down to check more rows. Use EXACT instead if capitalization must match.
How can I check whether a value exists in another column?
With the value in A2 and the comparison list in B2:B20, use =COUNTIF($B$2:$B$20,A2)>0. TRUE means the value is present; FALSE means it is not found.
Does COUNTIF match uppercase and lowercase differently?
No. COUNTIF is case-insensitive for text. To test capitalization as well as characters, use EXACT in a comparison or a case-sensitive formula suited to the size and structure of your data.
Can Excel highlight matching values automatically?
Yes. Select the values to check, create a conditional formatting rule using a formula such as =COUNTIF($B$2:$B$20,A2)>0, and choose a format. Confirm that the rule’s Applies to range is correct.
Why does Excel say there is no match when the values look the same?
Common causes include extra spaces, text-versus-number differences, dates stored as text, hidden characters, or a formula that points to the wrong range. Compare the underlying values and clean a safe copy of the data before adjusting the check.
Related
- Best Excel To-Do List Templates for Work and Teams
- Big 4 Audit Working Papers: Do Auditors Use Excel?
- Excel Average Keyboard Shortcut and Fastest Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MEDIAN Function Examples and How It WorksTo find the middle value in Excel, use the MEDIAN function: with your numbers in B2:B8, enter =MEDIAN(B2:B8).…
- Excel Conditional Formatting Formula Examples That WorkExcel conditional formatting formulas let you highlight cells or entire rows when a logical test is true. In…
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- Excel Shortcut to Paste a Formula: Keys and Tips in ExcelIn Excel for Microsoft 365 on Windows, copy the formula cell, select the destination, then press Ctrl+Alt+V, press…
- Excel IRR for Monthly Cash Flows: Formula and ExamplesFor equally spaced monthly cash flows in B2:B14, enter =IRR(B2:B14) to calculate the rate per month; the first…
- Excel Formula to Find and Replace Text in Cells: ExamplesTo replace text inside a cell with an Excel formula, use SUBSTITUTE. If the original text is in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.