How to Compare Two Columns in Excel with VLOOKUP Formula

Formulas & functions · Updated November 18, 2026

To compare two columns in Excel, check whether each value in the first list appears in the second with an exact-match VLOOKUP. With values in A2:A6 and D2:D5, enter this in B2 and fill down: =IF(A2="","",IF(ISNA(VLOOKUP(A2,$D$2:$D$5,1,FALSE)),"Missing","Found")). The result marks each nonblank value in column A as Found or Missing. This guide uses Microsoft Excel for Microsoft 365 on Windows; the same formula and ribbon features work in Excel for Mac and Excel for the web.

What You’ll Need

  • A workbook containing the two lists you want to compare. They can be on the same worksheet or different worksheets.
  • A spare column for the results, such as column B when the lists are in columns A and D.
  • A clear idea of which list is the reference list. VLOOKUP checks for each value in the first list against the reference list; it does not automatically compare both directions.

The lists do not need to be the same length or in the same order. This method is useful for checking customer names, product codes, employee IDs, invoice numbers, or any other values that should appear in both lists. For the most reliable result, make sure the values use the same format and do not contain unintended spaces.

Step-by-Step

  1. Arrange and identify the lists. In this example, the first list is in A2:A6 and the list to check against is in D2:D5. Column A contains Apple, Pear, Plum, Mango, and Kiwi. Column D contains Mango, Apple, Kiwi, and Peach. The different order is fine: VLOOKUP searches the reference range rather than comparing row 2 with row 2. Put a heading such as Check in B1 so the result column is easy to identify.
  2. Choose where the comparison should appear. Click B2, the first result cell alongside the first value in A2. A separate result column keeps your original lists unchanged and makes it easy to filter or review the outcome. If your data starts on a different row, use the cell on that same row for the formula.
  3. Enter the exact-match formula. With the first list in A2:A6 and the reference list in D2:D5, enter this in B2: =IF(A2="","",IF(ISNA(VLOOKUP(A2,$D$2:$D$5,1,FALSE)),"Missing","Found")). The outer IF leaves the result blank when the source cell is blank. Otherwise, VLOOKUP searches for A2 in the first column of D2:D5. The final argument FALSE tells Excel to look for an exact match. ISNA detects the #N/A result that VLOOKUP returns when it cannot find the value, and IF displays a clear status instead.
  4. Confirm the result for the first row. Press Enter. In the sample data, B2 should show Found because Apple appears in D2:D5. This formula does not require the matching value to be on the same row. For the other sample values, the expected outcomes are Pear: Missing, Plum: Missing, Mango: Found, and Kiwi: Found. These results show that three of the five values from the first list also occur in the reference list.
  5. Copy the formula down. Select B2, then drag the small square at the lower-right corner of the cell down through B6. You can also copy B2, select B3:B6, and paste. Excel adjusts the source reference as it fills: A2 becomes A3, A4, and so on. The dollar signs in $D$2:$D$5 keep the lookup range fixed, so every row is checked against the same reference list.
  6. Read or filter the results. Found means the value in column A appears at least once in column D. Missing means VLOOKUP did not find an exact match in that range. You can use the result column to review discrepancies, or use Excel’s filter controls to show only Missing rows. A Missing result does not by itself prove the values are genuinely different; check for extra spaces, different data types, or spelling variations before editing the source data.
  7. Check the other direction if you need a full comparison. The first pass answers, “Which values in A are absent from D?” It does not show values present in D but absent from A. To check the reference list back against the first list, put a heading in E1 and, with values in D2:D5 and the comparison range A2:A6, enter this in E2: =IF(D2="","",IF(ISNA(VLOOKUP(D2,$A$2:$A$6,1,FALSE)),"Missing","Found")). Fill it down to E5. In the sample, Peach is Missing from column A, while Mango, Apple, and Kiwi are Found. Checking both ways reveals differences in either list.
  8. Adjust the ranges for your worksheet. Replace the example cell references with the actual locations of your data. For example, if the list to check against runs from F2 to F500, use that as the lookup range and include dollar signs so it remains fixed when you copy the formula. Make sure the range covers every relevant entry, including the final row; a value below the selected range will be reported as Missing even if it appears farther down the worksheet.

VLOOKUP searches only the leftmost column of the range you give it. That is why the formula uses D2:D5 when column D is the list being searched. The third argument is 1 because the range contains one column and the formula needs to return that column’s value. If you use a wider range, keep the lookup list as its first column and choose the correct column number for the return value. For a simple presence check, a one-column range is easier to audit.

Common Mistakes

  • Leaving out FALSE. VLOOKUP’s final argument should be FALSE for an exact-match comparison. If it is omitted or set to TRUE, Excel can use an approximate match, which is unsuitable for checking whether a particular name or code exists and may give misleading results when the list is not sorted.
  • Not fixing the lookup range. Without dollar signs, the lookup range shifts as you fill the formula down. For example, the next formula might search D3:D6 instead of D2:D5 and overlook an entry near the top. Use absolute references such as $D$2:$D$5.
  • Expecting the formula to compare both lists at once. The first pass tests A against D only. A value can be Found in that direction while another value in D is absent from A. Run the reverse check as well when you need to identify all differences.
  • Comparing values that look alike but are stored differently. A number stored as text may not match the same number stored as a numeric value. Product codes with leading zeros can also be changed or interpreted differently if one column is numeric. Check the cell formats and source data before concluding that a reported mismatch is correct.
  • Missing spaces or spelling changes. “North Shore” and “North Shore ” contain different text because the latter has a trailing space. Small spelling differences also count as different values. VLOOKUP does not trim or standardize text for you.
  • Assuming a duplicate is a separate match. If the reference list contains the same value several times, VLOOKUP still returns the first matching value. For a Found/Missing check, duplicates do not change the status; the formula is not counting how many times a value appears.

Tips

  • Use a consistent key. Compare a stable identifier, such as an employee number or SKU, rather than a name that may be spelled or formatted in multiple ways. If names are the only available key, make sure both lists follow the same naming convention.
  • Keep the status labels consistent. Found and Missing make the result readable without requiring someone to interpret #N/A. You can replace those labels with wording that suits your worksheet, such as Present and Not present, as long as the text is enclosed in quotation marks in the formula.
  • Keep headings out of the lookup range. Start the range at the first data row, not at the column heading. If the range includes a header, the formula may search it unnecessarily, and careless row references can make it harder to spot an off-by-one error.
  • Use a suitable range size. Include all rows that could contain a match, but avoid selecting an unnecessarily large area if the workbook is very large. When the lists change regularly, consider converting each list into an Excel table so new rows can be included more easily.
  • Preserve the original data. Put cleanup or comparison results in a separate column first. This lets you review unexpected mismatches before changing names, codes, or formatting in the source lists.
  • Remember that matching is not case-sensitive. VLOOKUP treats entries such as “sku-a1” and “SKU-A1” as a match. If capitalization is meaningful in your data, this formula alone cannot distinguish those values; use a case-sensitive comparison approach instead.

Troubleshooting

Every result says Missing. First check that the VLOOKUP range begins with the column containing the values you want to search. Then confirm that the range includes the full list and that the formula uses FALSE as its last VLOOKUP argument. If those details are correct, inspect a value reported as Missing in both columns. Look for leading or trailing spaces, punctuation differences, and numbers stored as text in one list but as numbers in the other.

The result changes when you fill down. Check whether the lookup range has dollar signs around both its column letters and row numbers. A fixed reference such as $D$2:$D$5 should remain the same in every copied formula. The first argument should remain relative, so A2 changes to A3 as the formula moves to the next row.

The formula displays #N/A instead of Missing. Check that the formula includes ISNA around VLOOKUP and that the formula was entered in full. If you are using a different version of the formula, an unmatched value can produce #N/A directly. Also make sure the cell does not contain a typo in the formula or a lookup range that stops too early.

You see a formula error as soon as you enter it. Confirm that every opening parenthesis has a closing parenthesis and that the text labels have straight double quotation marks. The formula shown here uses commas as argument separators. Some regional settings require semicolons instead; if Excel rejects the commas, use the separator your installation expects while keeping the same arguments and order.

A blank source row is marked Found. The version in this guide includes an outer IF that leaves blank source cells blank. If you removed that part, a blank lookup value can match a blank cell in the reference range. Restore the blank check or exclude empty rows from the comparison.

A match seems to be missing because of leading zeros. Codes such as 00427 may be stored as text in one column and as the number 427 in another. Decide whether those zeros are meaningful. If they are, store both lists as text and preserve the zeros; do not convert identifiers to numbers merely to make the comparison work.

Frequently Asked Questions

Can VLOOKUP compare two columns with different numbers of rows?
Yes. The source list and lookup range can be different lengths. Set the lookup range to include the entire reference list, then fill the formula down alongside every value in the source list.

Does VLOOKUP need the two columns to be sorted?
No, not when you use FALSE for an exact match. Sorting may make the lists easier to review, but it is not required for this comparison.

Can I compare columns on separate worksheets?
Yes. Select or type the reference range on the other worksheet in the VLOOKUP formula, then keep that range fixed when filling down. The lookup column still needs to be the first column in the selected range.

Does Found mean the value appears exactly once?
No. Found means at least one matching value exists in the lookup range. VLOOKUP returns the first match, so this check does not count duplicates or report every occurrence.

Will this formula treat uppercase and lowercase as different?
No. VLOOKUP’s text matching is not case-sensitive, so uppercase and lowercase versions of the same text are treated as a match. Use a different method if capitalization must be distinguished.

Can I use the formula in Excel for Mac or Excel for the web?
Yes. VLOOKUP, IF, and ISNA are available in these versions, and the formula works the same way. The exact keyboard shortcuts can differ by operating system, but you can copy and fill the formula using the worksheet controls in each version.

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.