Excel Formulas to Find Matching Data in Your Lists

Formulas & functions · Updated November 19, 2026

To check whether a value appears in another Excel list, use COUNTIF; to return information from the matching row, use XLOOKUP. COUNTIF tells you whether—and how often—a value appears, while XLOOKUP finds the matching row and returns a value from another column.

Check Whether a Value Appears in a List

Suppose the value you want to check is in A2 and a reference list is in D2:D100. This formula displays a clear result depending on whether Excel finds the value. For example, if A2 contains Orchid and Orchid appears anywhere in D2:D100, the result is Match.

With the value to check in A2 and the comparison list in D2:D100:

=IF(COUNTIF($D$2:$D$100,A2)>0,"Match","No match")

COUNTIF counts cells that meet a condition. The greater-than-zero test turns that count into a yes-or-no result. The dollar signs keep the reference list fixed if you copy the formula down a column, while A2 adjusts to A3, A4, and so on.

If you want the number of occurrences rather than a label, use COUNTIF by itself. With the same layout, it returns a number: if Orchid occurs twice in D2:D100, the result is 2.

=COUNTIF($D$2:$D$100,A2)

Choose the Right Kind of Match

The best formula depends on what you mean by matching. A quick check that two lists overlap calls for a count or yes-or-no result. Finding a related value, such as a product name for an ID, calls for a lookup. Returning every row that meets a condition calls for a filter.

  • Check whether a match exists: COUNTIF for one condition, or COUNTIFS for several.
  • Return a related value: XLOOKUP in Microsoft 365 or Excel 2021 and later; INDEX with MATCH is a widely compatible alternative.
  • Return all matching rows: FILTER in Microsoft 365 or Excel 2021 and later.
  • Find text that contains a phrase: use wildcards with COUNTIF, or use a search-based formula when you need to return information.

Most of these formulas perform exact matching by default: the whole lookup value must match the whole cell value. Exact matching is usually what you want for IDs, email addresses, and product codes. A partial match is different: it deliberately finds a cell that contains only part of the search text.

Return Information with XLOOKUP

XLOOKUP searches one range and returns a value from the corresponding position in another range. For example, put product IDs in A2:A20, product names in B2:B20, and the ID to find in E2. If E2 contains P104 and that ID’s name in column B is Desk lamp, the formula returns Desk lamp.

With IDs in A2:A20, names in B2:B20, and the lookup ID in E2:

=XLOOKUP(E2,$A$2:$A$20,$B$2:$B$20,"Not found")

XLOOKUP uses an exact match by default, so you generally do not need to add a match-mode argument for IDs. The fourth argument supplies a friendly result when there is no match, rather than displaying the #N/A error. XLOOKUP can search in either direction and does not require the return column to be to the right of the lookup column.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available in older perpetual versions such as Excel 2019. If you share a workbook with someone using an older version, use INDEX and MATCH or confirm that their Excel can calculate the function.

Use INDEX and MATCH for Compatibility

INDEX and MATCH work together: MATCH finds the position of a value, and INDEX returns the value at that position in a different range. In the product example, MATCH locates the ID from E2 in column A, and INDEX uses that position to return the product name from column B.

With IDs in A2:A20, names in B2:B20, and the lookup ID in E2:

=IFERROR(INDEX($B$2:$B$20,MATCH(E2,$A$2:$A$20,0)),"Not found")

The zero in MATCH requests an exact match. IFERROR replaces an error—such as the #N/A result when the ID is absent—with Not found. INDEX and MATCH are useful when a workbook needs to work in older Excel versions, and they can return a value from a column on either side of the lookup range. For a straightforward lookup in a recent version, XLOOKUP is usually easier to read.

Match Rows Using More Than One Condition

Sometimes a value is not unique by itself. A transaction may need to match both a customer and a date, or a product may be identified by both its size and color. COUNTIFS checks whether a row meets multiple conditions at once. In this example, column A contains customer names, column B contains regions, and G2 and H2 contain the customer and region to check. If that combination appears in the data, the result is Match.

With customer names in A2:A100, regions in B2:B100, the customer to check in G2, and the region in H2:

=IF(COUNTIFS($A$2:$A$100,G2,$B$2:$B$100,H2)>0,"Match","No match")

To return information for a row meeting both conditions, XLOOKUP can search for the two tests together. Here, column C contains the value to return. If G2 is Avery and H2 is West, and the first row meeting both conditions has a value of 325 in column C, the formula returns 325.

With customer names in A2:A100, regions in B2:B100, return values in C2:C100, and the criteria in G2 and H2:

=XLOOKUP(1,($A$2:$A$100=G2)*($B$2:$B$100=H2),$C$2:$C$100,"Not found")

Each comparison produces TRUE or FALSE values; multiplying the results makes a row that meets both tests equal 1. XLOOKUP then finds the first such row. This array-based formula works in Microsoft 365 and Excel 2021 or later. If the data has duplicate rows meeting the conditions, it returns the first one.

Find Partial Text or Return Every Match

COUNTIF supports wildcard characters when you want to find text that contains a phrase rather than an exact value. An asterisk represents any number of characters, while a question mark represents one character. For example, if A2 contains lamp, this formula counts entries such as Desk lamp or Lamp shade in D2:D100. It returns 2 if those are the only two matches.

With the search text in A2 and descriptions in D2:D100:

=COUNTIF($D$2:$D$100,"*"&A2&"*")

Because the asterisks go on both sides of A2, Excel looks for the search text anywhere in each cell. COUNTIF is not case-sensitive, and the question-mark wildcard matches one character. A tilde can be used to search for a literal wildcard character. Be aware that a short search term may match many unrelated descriptions.

When you need a list of all matching records, FILTER can return multiple results that spill into nearby cells. In this example, A2:A20 contains IDs, B2:B20 contains names, and E2 contains the ID to search for. If the ID appears on three rows, the formula returns the three corresponding names. If it appears nowhere, the result is No matches.

With IDs in A2:A20, names in B2:B20, and the lookup ID in E2:

=FILTER($B$2:$B$20,$A$2:$A$20=E2,"No matches")

FILTER is available in Microsoft 365 and Excel 2021 or later. Its results spill into adjacent cells, so make sure those cells are empty; otherwise, Excel may show a #SPILL! error. FILTER returns every matching name in this example, unlike XLOOKUP, which returns only one match.

Diagnose Matches That Excel Misses

A formula can report no match even when two cells look identical. Often the values differ in a way that is hard to see. Check the following before changing the formula:

  • Leading or trailing spaces: imported data may contain extra spaces. TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words.
  • Numbers stored as text: a number such as 104 stored as text may not behave like the numeric value 104. Convert the data consistently rather than relying on how the cells look.
  • Hidden characters: copied data can contain nonprinting characters. CLEAN removes many nonprinting characters, but it does not remove every unusual space character.
  • Different spellings or punctuation: check hyphens, apostrophes, accents, and other characters in both lists.
  • Range problems: confirm that the lookup range and return range have the same number of rows, and that copied formulas still point to the intended lists.

To clean a single text value, you can combine TRIM and CLEAN. With the original text in A2, this returns a cleaned version in the formula cell; for example, extra ordinary spaces around Desk lamp are removed. This does not alter the source cell. Compare cleaned versions of both lists if you want to use cleaned data for matching.

With the text to clean in A2:

=TRIM(CLEAN(A2))

COUNTIF and XLOOKUP are generally not case-sensitive for ordinary text matching. If capitalization must distinguish one value from another, use a case-sensitive approach such as EXACT in a formula designed for that requirement. Also note that a lookup returning the wrong record may not be an error: if the lookup value is duplicated, XLOOKUP normally returns the first matching row. Verify that the key column is unique when each key is supposed to identify only one record.

Highlight Matching Values Instead of Returning Them

If the goal is to visually compare two lists, conditional formatting can highlight matches directly in the worksheet. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the rule to apply to A2:A100, enter the formula below, choose a format, and confirm the rule.

With values in A2:A100 to check against D2:D100, and the selected range starting at A2:

=COUNTIF($D$2:$D$100,A2)>0

The fixed reference keeps the comparison list in place, while A2 changes for each row in the selected range. The rule highlights cells in column A whose values occur in column D; it does not create a separate list or change the cell contents. On Mac and Excel for the web, the same conditional-formatting feature is available, though the dialog layout can differ from the Windows desktop version.

Frequently Asked Questions

How do I compare two columns in Excel and find values in both?
Use COUNTIF to check each value in one column against the other. To mark matches, put the check formula beside the first list and fill it down; use conditional formatting if you want to highlight the matching cells instead.

How can I return all matches instead of just the first one?
Use FILTER in Microsoft 365 or Excel 2021 and later. XLOOKUP returns one match, while FILTER can return every row that meets the condition.

Why does XLOOKUP return #N/A when the value is visible?
The lookup value may contain extra spaces, hidden characters, or a different data type from the corresponding list entry. Compare the underlying values and clean or convert both sides consistently.

Does XLOOKUP find partial matches by default?
No. XLOOKUP uses exact matching by default. For partial text searches, use a wildcard-enabled function or build a search formula specifically for the text pattern you need.

Which formula should I use if a lookup value appears more than once?
Use COUNTIF to count duplicates, FILTER to return all matching rows, or XLOOKUP if you intentionally want one result. By default, XLOOKUP returns the first matching entry, so check whether that is appropriate for your data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.