To compare two lists in Excel, use an exact-match VLOOKUP to check whether each item in one list appears in the other. For example, with the first list in A2:A6 and the list to check in D2:D6, enter =IF(ISNA(VLOOKUP(D2,$A$2:$A$6,1,FALSE)),"Missing","Match") in E2 and fill it down. The results show which entries match and which are absent. Before starting, make sure both lists use the same kind of identifier, such as an ID, product code, or consistently spelled name.
What You’ll Need
- An Excel workbook with the two lists you want to compare. The examples below use Microsoft Excel for Microsoft 365 on Windows.
- A common value to match, such as an employee ID or product number. VLOOKUP searches for this value in the first column of its lookup range.
- An empty column beside the list you want to check, where you can put the comparison results.
- Optional: related data to compare as well, such as a price or status stored in another column beside the lookup value.
You do not need to sort the lists when you use an exact match. If you use Excel for Mac or Excel for the web, the formulas below work the same way; some ribbon and keyboard details may differ slightly.
Step-by-Step
- Identify the two lists and what counts as a match. Decide which list is your reference list and which list you want to check. For example, suppose A2:A6 contains the IDs in a master list and D2:D6 contains IDs from a newer report. The question is whether each report ID exists in the master list. Choose a value that reliably identifies each record. Names may be duplicated or spelled differently, while a unique customer or product ID is usually a better key.
- Check the layout of the reference data. VLOOKUP searches only the leftmost column of the range you give it. If you are checking IDs in column D against IDs in column A, use a lookup range beginning in A. If the values you need to match are not in the first column of the selected range, rearrange or copy the relevant data into a suitable layout, or use a different lookup method. Also make sure the range includes all reference rows, not just the first few visible entries.
- Add a result heading. In E1, type a heading such as “Comparison” or “Found in master list.” A clear heading makes the output understandable when you filter, share, or revisit the workbook. Keep the result column next to the list being checked so that each result stays aligned with its corresponding ID.
- Enter an exact-match formula for list membership. With master IDs in A2:A6, checked IDs in D2:D6, and the result going in E2, enter
=IF(ISNA(VLOOKUP(D2,$A$2:$A$6,1,FALSE)),"Missing","Match"). VLOOKUP looks for the value in D2 in the first column of A2:A6. The 1 tells it to return the value from the first column, and FALSE requires an exact match. ISNA detects the #N/A result produced when there is no match; IF then displays “Missing” or “Match” instead of showing the error. - Check the result on a known example. Suppose A2:A6 contains 104, 108, 112, 117, and 121, while D2:D4 contains 104, 112, and 120. The formula in E2 returns “Match” because 104 is in the reference list. After copying it down, E3 returns “Match” for 112 and E4 returns “Missing” for 120. Confirm one known match and one known non-match before applying the formula to a large list; that simple check can reveal a reversed range or incorrect cell reference.
- Lock the reference range, then fill the formula down. The dollar signs in $A$2:$A$6 keep the master range fixed as the formula is copied. The checked cell D2 is not locked, so it changes to D3, D4, and so on. Select E2 and use the fill handle—the small square at the bottom-right corner of the selected cell—to copy the formula down beside the checked entries. In Windows Excel, Ctrl+D fills a selected range with the formula from its top cell; on Mac, use Command+D. You can also double-click the fill handle when there is adjacent data to guide the fill.
- Review and filter the comparison results. Scan the output for unexpected “Missing” entries and check that the filled formulas reach the last row of your list. To focus on exceptions, select the data and choose Data > Filter, then use the filter arrow in the result heading to show only “Missing.” Filtering does not change the formulas; it simply helps you review the rows that need attention. Clear the filter when you want to see the complete comparison again.
- Compare a related value, not just whether a record exists. VLOOKUP can return a value from another column in the reference table. For example, with IDs in A2:A5, prices in B2:B5, IDs to check in D2:D5, and reported prices in E2:E5, enter this in F2 to compare each report price with the master price:
=IFERROR(IF(VLOOKUP(D2,$A$2:$B$5,2,FALSE)=E2,"Same","Different"),"Not found"). The 2 tells VLOOKUP to return the second column of A2:B5, which is the price. For a matched ID with a master price of 19.99 and a report price of 19.99, the result is “Same”; if the report says 20.49, the result is “Different.” An ID absent from the reference range returns “Not found.” Copy this formula down to check the remaining rows. - Save or preserve the comparison output. If you need a permanent snapshot rather than formulas that update when source data changes, copy the result cells and use Home > Paste > Values to replace them with their displayed text. Keep a copy of the original formulas or workbook if you may need to refresh the comparison later. Formula results are only as reliable as the source data and the chosen matching key.
Common Mistakes
Leaving out FALSE. If the fourth VLOOKUP argument is omitted or set to TRUE, Excel can perform an approximate match, which may return an incorrect result for a list comparison. Use FALSE for exact matching. Sorting is not required for this exact-match setup.
Using a range with the wrong first column. VLOOKUP cannot search to the left of the first column in its table array. If the lookup value is in D2, the first column of the selected reference range must contain the values Excel should search. A range that starts one column too far to the right can produce #N/A or an incorrect return.
Forgetting absolute references. If the reference range is written as A2:A6 without dollar signs, filling the formula down shifts it to A3:A7, then A4:A8. Use $A$2:$A$6 to keep it fixed. Conversely, do not lock the checked cell if you want it to update on each row.
Assuming visually similar values are identical. A number stored as text may not match a number stored as a numeric value, and extra spaces or different punctuation can make names or codes unequal. Check the underlying cell values and formatting rather than relying only on how they look.
Returning the wrong column. The column index is counted from the first column of the selected range, not from column A on the worksheet. In A2:C20, an index of 2 returns column B. An index larger than the number of columns in the range produces #REF!.
Tips
Use stable, unique keys wherever possible. If you compare people by name, “Alex Lee” may appear more than once or may be entered as “Alex Lee” with an extra space. An employee number is safer if it is consistent in both lists. If there is no unique key, a match may confirm that a value exists without identifying the correct record.
Make the comparison easier to audit by keeping the original columns intact and adding a separate result column. Avoid editing source values just to make a formula work until you have confirmed what is inconsistent. If leading or trailing spaces are the issue, a cleaned helper column can help; for example, with source text in A2, =TRIM(A2) removes ordinary extra spaces. Apply the same cleanup approach to both lists, and remember that TRIM does not remove every unusual nonbreaking space.
For a large worksheet, convert each data range into an Excel table using Home > Format as Table. Tables make headings and ranges easier to manage as rows are added. When a comparison will be repeated regularly, document which list is the reference, which field is the key, and whether the result means “found” or “identical in every field.” These are different checks: a matching ID does not by itself prove that the rest of the record matches.
If a match should allow for a small numeric difference, decide on a tolerance rather than comparing decimals blindly. For example, you may need to account for rounding in calculated values. In that case, retrieve the reference number and compare the difference against a threshold appropriate to your data; do not treat near-equal values as equal without defining that rule first.
Troubleshooting
Every expected match says “Missing.” First verify that the lookup range begins with the reference IDs and includes the correct rows. Check whether the IDs have different data types, such as numeric 104 in one list and text “104” in the other. Look for spaces, leading zeros, or punctuation differences. For codes, leading zeros may be significant: converting a text code such as 00104 to a number changes its value. Standardize both columns in a deliberate way before comparing them.
You see #N/A instead of a label. The basic VLOOKUP result is #N/A when it cannot find an exact match. In the membership formula, ISNA should turn that condition into “Missing.” If you still see the error, check that the formula was entered correctly and that the cell references point to the intended columns. In the related-value formula, IFERROR handles a lookup error and displays “Not found”; if you remove IFERROR, an absent ID will show #N/A.
You see #REF! or a wrong returned value. Confirm that the column index is no greater than the number of columns in the lookup range. Also count from the range’s first column: for A2:C10, 1 means A, 2 means B, and 3 means C. If you are only checking whether an item exists, use 1 with a one-column range to avoid confusion.
The results change after filling down. Inspect the formula in a lower row. The checked-cell reference should advance (for example, D2 to D3), while the reference range should remain fixed ($A$2:$A$6). If the formula stops before the bottom of the data, use the fill handle again or select the intended output range and fill down. In Excel for the web, the same formula works, and you can use the fill handle or copy and paste it.
Some rows match the wrong record. Check whether the key appears more than once in the reference list. VLOOKUP returns the first matching row, so duplicate keys can make a related-value comparison ambiguous. Remove or resolve duplicates, or include another field in a composite key if the data requires more than one value to identify a record uniquely.
Frequently Asked Questions
Do the two lists need to be sorted for VLOOKUP?
No, not when the formula uses FALSE for an exact match. Sorting can make large lists easier for a person to inspect, but it is not required for this comparison formula.
Can VLOOKUP compare two columns in the same worksheet?
Yes. The two lists can be on the same worksheet or on different worksheets. If the reference list is on another sheet, include the sheet name in the range, such as Sheet2!$A$2:$A$100. If the sheet name contains spaces, surround it with apostrophes, for example 'Master List'!$A$2:$A$100.
Can VLOOKUP tell me which items are missing from either list?
Yes. The example checks each item in the second list against the first. To find items in the first list that are absent from the second, reverse the roles: put the first list’s value in the lookup cell and use the second list as the lookup range. You need to run the comparison in both directions to identify items unique to each list.
Why does VLOOKUP return the first result when there are duplicates?
VLOOKUP returns the first matching row it encounters in the lookup range. If duplicate keys have different associated values, it cannot decide which record you intend. Check for duplicates and use a unique identifier or a more specific matching key.
Is VLOOKUP case-sensitive?
No. A standard VLOOKUP match treats uppercase and lowercase text as equivalent, so “abc” and “ABC” can match. If capitalization itself matters, VLOOKUP alone is not a case-sensitive comparison tool.
Does this work in Excel for Mac?
Yes. VLOOKUP formulas use the same syntax in Excel for Mac and Windows. On Mac, Command+D fills down a selected range; on Windows, use Ctrl+D. Excel for the web also supports VLOOKUP, though the ribbon layout can differ from the Windows desktop app.
Related
- How to Center an Excel Page for Printing
- How to Center an Excel Spreadsheet on a Page for Printing
- How to Center an Excel Table in Word Without Distorting It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Concatenate Formula Examples for Text and CellsWith a first name in A2 and a last name in B2, join them with a space using…
- PHP String Handling Functions: Common Uses and ExamplesPHP string handling functions are built-in tools for measuring, searching, extracting, replacing, splitting, comparing, and formatting text. Choose…
- Excel Formulas for Difference in Numbers: A Practical GuideTo find the difference between two numbers in Excel, subtract the earlier or starting value from the later…
- ISBLANK Formula Examples in ExcelIn Excel, ISBLANK checks whether a referenced cell is genuinely empty and returns TRUE or FALSE. For example,…
- Color Picker in Excel: Choose and Match Colors PreciselyIn Excel for Microsoft 365 on Windows, select the cell or range, then go to Home > Fill…
- If Excel Cell Contains Text: Useful Formulas and ExamplesWith the text to check in A2, use =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No") to test whether the cell contains “apple” anywhere; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.