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?

  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:


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

  • 6
    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 '09 at 19:55
  • 4
    "Name a Range" doesn't appear for me? Tested on Excel 2010 and 2016. – KERR Jul 14 '17 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 '18 at 18:21
  • If, like me, you don't realize at first where to enter "ColumnToSearch" (step 3), enter it as the name of the named range you are creating. – DaveL17 May 7 at 11:26
  • "Name a Range" appears as Define Name... (Excel 2019) – oliver-clare Aug 12 at 13:55

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.

  • I used "=ISODD(COUNTIF(A:A,B1)=0)" to print true for '1' or false for '0' – Ramraj Sep 20 '16 at 10:13

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.

  • if you have a value in column B repeated, this will not highlight them – magodiez Nov 13 '15 at 16:18
  • Surely this is the simplest and most relevant answer? No formulas required. – KERR Jul 12 '17 at 23:24

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:


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.

  • 3
    =MATCH(B2,$A:$A,0) worked for me. – nilgun Sep 6 '16 at 10:37

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


Comparing two columns of names and returning missing names

  • This solution is good, but depends on the source columns being sorted alphabetically, or else it retrieves duplicates. – Steve Taylor Jan 4 '17 at 8:36

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.

  • 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 '14 at 17:48

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:


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


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

protected by Nifle May 12 '15 at 16:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.