143

I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A.

What's the quickest way to do this?

Improve this question

8 Answers 8

Reset to default
140
  1. Select the list in column A
  2. Right-Click and select Name a Range...
  3. Enter "ColumnToSearch"
  4. Click cell C1
  5. Enter this formula: =MATCH(B1,ColumnToSearch,0)
  6. Drag the formula down for all items in B

If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

If you'd like to return the unfound value and return empty string for found values

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)
Improve this answer
7
  • 7
    Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells
    – Kije
    Dec 10, 2009 at 19:55
  • 7
    "Name a Range" doesn't appear for me? Tested on Excel 2010 and 2016.
    – KERR
    Jul 14, 2017 at 1:13
  • If, like @KERR and I, you don't have "Name a Range...", just use A:A or your range in lieu of ColumnToSearch in the formulae.
    – Michael
    Feb 13, 2018 at 18:21
  • 7
    "Name a Range" appears as Define Name... (Excel 2019)
    – m-smith
    Aug 12, 2019 at 13:55
  • 2
    Also "Name a Range" only appears if you select a subset of the column, clicking the column header to select the entire column doesn't let you name a range.
    – Quantic
    May 3, 2021 at 19:38
42

Here's a quick-and-dirty method.

Highlight Column B and open Conditional Formatting.

Pick Use a formula to determine which cells to highlight.

Enter the following formula then set your preferred format.

=countif(A:A,B1)=0
Improve this answer
2
  • 1
    I used "=ISODD(COUNTIF(A:A,B1)=0)" to print true for '1' or false for '0'
    – Ramraj
    Sep 20, 2016 at 10:13
  • For unknown reasons the countif() method doesn't work reliably when specifying a multi-column array (e.g. =countif(B:C,A1)=0) - Excel 2016.
    – HamishKL
    Mar 1, 2021 at 4:08
29

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.

Improve this answer
4
  • if you have a value in column B repeated, this will not highlight them
    – magodiez
    Nov 13, 2015 at 16:18
  • 2
    Surely this is the simplest and most relevant answer? No formulas required.
    – KERR
    Jul 12, 2017 at 23:24
  • 1
    Nothing happens when doing this.
    – Overmind
    Oct 16, 2019 at 8:19
  • 1
    Easily the best answer. Simplest.
    – NedStarkOfWinterfell
    Feb 11, 2020 at 19:11
18

Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:

=MATCH(B2,$A$2:$A$287,0)

Then click and drag down.

A cell with #N/A means that the value directly next to it in column B does not show up anywhere in the entire column A.

Please note that you need to change $A$287 to match your entire search array in Column A. For instance if your data in column A goes down for 1000 entries it should be $A$1000.

Improve this answer
2
  • 6
    =MATCH(B2,$A:$A,0) worked for me.
    – nilgun
    Sep 6, 2016 at 10:37
  • what do you mean, 'click and drag down'? This just makes the cells turn grey.
    – john k
    Oct 16 at 18:01
4

See my array formula answer to listing A not found in B here:

=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

Comparing two columns of names and returning missing names

Improve this answer
1
  • This solution is good, but depends on the source columns being sorted alphabetically, or else it retrieves duplicates.
    – Steve can help
    Jan 4, 2017 at 8:36
4

My requirements was not to highlight but to show all values except that are duplicates amongst 2 columns. I took help of @brenton's solution and further improved to show the values so that I can use the data directly:

=IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "")

Copy this in the first cell of the 3rd column and apply the formula through out the column so that it will list all items from column B there are not listed in column A.

Improve this answer
1
  • 1
    There is an error in the formula above =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "") should be =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), B2, "")
    – user374241
    Oct 1, 2014 at 17:48
1

Thank you to those who have shared their answers. Because of your solutions, I was able to make my way to my own.

In my version of this question, I had two columns to compare -- a full graduating class (Col A) and a subset of that graduating class (Col B). I wanted to be able to highlight in the full graduating class those students who were members of the subset.

I put the following formula into a third column:

=if(A2=LOOKUP(A2,$B$2:$B$91),1100,0)

This coded most of my students, though it yielded some errors in the first few rows of data.

Improve this answer
-3

in C1 write =if(A1=B1 , 0, 1). Then in Conditional formatting, select Data bars or Color scales. It's the easiest way.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .