Match Excel Values: Exact Matches, Lookups, and Lists

Formulas & functions · Updated November 18, 2026

To find an exact match in Excel, use MATCH with match type 0. If the value to find is in E2 and the search list is A2:A6, enter =MATCH(E2,$A$2:$A$6,0); Excel returns the matching item’s position within that range, or #N/A if it is absent. To return information from another column, use XLOOKUP in Microsoft 365.

What matching values means in Excel

“Match values” can describe several different jobs. You might want to check whether one value appears in a list, find the position of a value, retrieve a related value from another column, compare two cells, or highlight overlaps between two lists. The right approach depends on which result you need.

For example, imagine a product table with IDs in A2:A6 and product names in B2:B6. A separate cell, E2, contains an ID to search for. Finding the matching ID is not the same as returning its product name: the first task needs a presence check or position, while the second needs a lookup that returns a value from column B.

Excel’s usual exact-match lookups are not case-sensitive. Thus, a search for “abc” can match “ABC.” A match also depends on the underlying value, not just how a cell looks. A number displayed with a currency format remains a number, while a number stored as text is text; those may fail to match even if they appear identical on screen.

Find an exact match or its position

MATCH is useful when you need the position of an item within a range. With the lookup value in E2 and IDs in A2:A6, use =MATCH(E2,$A$2:$A$6,0). The final argument, 0, asks for an exact match. If E2 contains the value in A4, the result is 3 because A4 is the third cell in A2:A6—not because it is worksheet row 3.

The dollar signs make the search range absolute. If you copy the formula down, the reference to E2 can change to E3, while the search range remains A2:A6. If Excel finds no exact match, MATCH returns #N/A. That error can be useful because it clearly signals that the value was not found.

To return a simple TRUE or FALSE instead of a position or error, combine MATCH with ISNUMBER. With E2 as the value to find and A2:A6 as the list, enter =ISNUMBER(MATCH(E2,$A$2:$A$6,0)). It returns TRUE when the match exists and FALSE when it does not.

Older formulas sometimes use VLOOKUP for matching. If you use it, specify an exact match with FALSE as its final argument. With IDs in A2:A6, names in B2:B6, and the lookup ID in E2, the formula =VLOOKUP(E2,$A$2:$B$6,2,FALSE) returns the name from the second column. VLOOKUP searches only the first column of its selected table and returns data to the right, so it is less flexible when the return column is on the left.

Return a related value with XLOOKUP

In Microsoft 365, XLOOKUP is a straightforward option for finding a value and returning related information. It is also available in Excel 2021. With IDs in A2:A6, product names in B2:B6, and the ID to find in E2, use =XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6). If E2 matches the ID in A4, Excel returns the product name from B4.

XLOOKUP uses exact matching by default, and its lookup and return ranges are separate. You can therefore search one column and return a value from a column on either side. It returns the first matching result when the lookup value occurs more than once. If duplicates are possible, decide whether the first result is acceptable or whether you need to clean the data or report all matching records another way.

If you want a custom message instead of #N/A, XLOOKUP has an optional “if not found” argument. With the same cell layout, =XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,"Not found") displays Not found when the ID is absent. Avoid replacing every error with a blank without thinking it through: a blank can make a missing record harder to distinguish from a genuinely empty return cell.

In Excel for the web and on Mac, the formula works the same way when the function is available. Ribbon wording or layout can vary slightly, but the formula syntax and comma separators shown here are for English-language Excel settings; some regional settings use semicolons instead.

Check whether two lists overlap

To flag values in one list that also appear in another, use COUNTIF. Suppose the first list is in A2:A100 and the comparison list is in B2:B100. In C2, enter =COUNTIF($B$2:$B$100,A2)>0, then fill down. TRUE means the value from column A occurs at least once in column B; FALSE means it does not.

This approach is handy for reconciling customer IDs, checking an inventory export against a catalog, or finding names that occur in both event lists. It checks each A value against the entire B range, rather than comparing A2 only with B2. That distinction matters when the same records are arranged in a different order.

You can also use the same logic to highlight matches. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the two lists in A2:A100 and B2:B100, enter =COUNTIF($B$2:$B$100,A2)>0 as the rule formula, choose a format, and confirm. The relative A2 reference adjusts for each selected row; the dollar signs keep the comparison range fixed.

One caution: COUNTIF treats the characters * and ? as wildcards in criteria. If values in your list contain those characters, a count may behave like a pattern search rather than a literal text comparison. Check the data and the function’s wildcard rules before relying on such results.

Compare cells row by row

Matching two lists is different from checking whether each pair of cells on the same row is equal. If you want to compare A2 with B2, enter =A2=B2 in C2 and copy it down. Excel returns TRUE when the two values are equal and FALSE when they are not. This is useful for comparing two versions of a table when corresponding records are already aligned.

That comparison is also case-insensitive for text. To distinguish uppercase and lowercase letters, use EXACT. With text in A2 and B2, =EXACT(A2,B2) returns TRUE only when their text matches exactly, including letter case. EXACT compares text and returns FALSE if the strings differ; it is not a substitute for looking up a value across a list.

For example, a row-by-row comparison can identify a changed price only if the same product is on the same row in both tables. If rows might have moved, first match on a stable identifier such as a product ID, then compare the related fields. Comparing row 12 in one export with row 12 in another can produce misleading results when the exports are sorted differently.

Fix common reasons matches fail

A formula can be correct and still return FALSE or #N/A because the source values are inconsistent. Before changing the lookup function, inspect the data itself. Common causes include:

  • Numbers stored as text: “123” stored as text may not match numeric 123. Check the values’ types and convert the data consistently rather than changing cell formatting alone.
  • Extra spaces or hidden characters: A name can appear identical while containing a leading space, a trailing space, or a nonprinting character. Cleaning imported data may be necessary before matching.
  • Different date values: A date may include a time component that is not visible in the cell’s display. Two cells can look like the same day but have different underlying date-time values.
  • Different identifiers: Leading zeros can matter for codes such as 0042. Decide whether an identifier is numeric or text, and keep that representation consistent throughout the relevant columns.
  • Duplicate lookup values: A lookup that returns the first match may appear incomplete if several rows share the same key. Check whether the key is supposed to be unique.

Formatting cells as Text or Number does not always convert values already stored in the cells. Likewise, applying a date format changes how a value is displayed, not necessarily the underlying value being compared. For imported data, check a few examples in the formula bar and test the conversion on a copy before changing a large range.

Choose the right matching method

Use the simplest method that gives the result you actually need:

  • Choose MATCH when you need an item’s position within a range.
  • Choose ISNUMBER(MATCH(...)) when you need a TRUE/FALSE presence check.
  • Choose XLOOKUP to retrieve a related value in Microsoft 365 or Excel 2021.
  • Choose COUNTIF to count occurrences or test whether a value appears in a comparison list.
  • Compare cells directly when the two values are meant to be on the same row; use EXACT if letter case matters.

For workbooks shared with people using older Excel versions, consider compatibility before using newer functions. XLOOKUP is available in Microsoft 365 and Excel 2021, but not in Excel 2019 or earlier. MATCH, COUNTIF, VLOOKUP, ISNUMBER, and EXACT are longstanding functions and are more suitable when older installations must open and calculate the workbook.

Frequently Asked Questions

How do I match two columns in Excel?
To check whether each value in A2:A100 appears anywhere in B2:B100, use =COUNTIF($B$2:$B$100,A2)>0 in a helper column and fill down. This checks for overlap regardless of row order.

Why does MATCH return #N/A when the value looks identical?
Check for text-versus-number differences, hidden spaces, nonprinting characters, date-time values, and inconsistent leading zeros. The displayed appearance of two cells does not guarantee that Excel stores them as the same value.

Does MATCH distinguish uppercase and lowercase?
No. MATCH’s normal exact-match mode is case-insensitive. Use EXACT for a case-sensitive comparison between two text cells, or build a more specific formula if you need case-sensitive searching across a list.

What does the 0 mean in MATCH?
It tells MATCH to search for an exact match. It also avoids the sorted-data assumptions associated with approximate match modes. For ordinary ID, name, or code matching, 0 is usually the appropriate setting.

Can Excel highlight matching values automatically?
Yes. Use a Conditional Formatting formula such as =COUNTIF($B$2:$B$100,A2)>0 for a selection in A2:A100. The rule highlights each selected value that appears in the comparison range, and it updates when the workbook recalculates.

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.