Excel intermediate compare lists excel formulas unique values common values

How to Compare Two Lists in Excel?

Compare Two Lists in Excel is a Excel function that checks if each value in list 1 exists in list 2 and labels it as 'unique to list 1' or 'common'.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Comparing two lists can be challenging, but with the right formula, you can easily identify unique and common items.

Generate This Formula Browse Library

The Formula

Prompt

"Find values that exist in one list but not the other, and identify items common to both lists"

Excel 
=IF(ISERROR(MATCH(A1, B:B, 0)), "Unique to List 1", "Common")

This formula checks if each value in List 1 exists in List 2 and labels it as 'Unique to List 1' or 'Common'.

Step-by-Step Breakdown

  1. Step 1: Use the MATCH function to check if a value from List 1 exists in List 2.
  2. Step 2: The ISERROR function determines if the MATCH function returns an error (meaning the value is not found).
  3. Step 3: The IF function returns 'Unique to List 1' if the value is not found, otherwise it returns 'Common'.
  4. Step 4: Drag the formula down to apply it to all values in List 1.

Edge Cases & Warnings

  • Empty lists may cause unexpected results.
  • Duplicates in either list can affect the count of unique values.
  • Different data types (e.g., text vs. numbers) can lead to mismatches.
  • Leading or trailing spaces in values can cause false negatives.

Examples

Prompt

"List 1: A1:A5 = {1, 2, 3, 4, 5}; List 2: B1:B5 = {4, 5, 6, 7, 8}"

Excel 
A1 returns 'Unique to List 1', A4 returns 'Common'
Prompt

"List 1: A1:A3 = {'apple', 'banana', 'cherry'}; List 2: B1:B3 = {'banana', 'date', 'fig'}"

Excel 
A1 returns 'Unique to List 1', A2 returns 'Common'

Frequently Asked Questions

How do I find values unique to List 2?

You can modify the formula to check List 2 against List 1.

Can I compare more than two lists?

Yes, but you will need to adjust the formula accordingly.

What if my lists are in different sheets?

You can reference the other sheet in the formula by including the sheet name.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples