Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Hi this is a repeat of the question: Excel: Find Items in one column that are not in another column

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?

However, the answer given below to in the above-menitoned thread no longer applies to Mac Excel 2011. E.g. there is no "name-a-range" option available on right click etc.. Therefore I am asking this again.

Select the list in column A Right-Click and select Name a Range... Enter "ColumnToSearch" Click cell C1 Enter this formula: =MATCH(B1,ColumnToSearch,0) 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(ISNA(MATCH(B1,ColumnToSearch,0)),FALSE,TRUE)

How should this answer be restated to apply to Mac Excel 2011?

share|improve this question

Conditional format of Column B with this formula works for Excel 2007 =ISNA(MATCH(B1,A:A,0)>0).

share|improve this answer

For some reason, Excel for Mac does not have Name Manager or a button on the ribbon for defining a name. The workaround per this Office help page:

Go to your system preferences. In the Keyboard Preferences go to the Keyboard tab.

Check the box by Use All F1, F2, etc. keys as standard function keys.

Now open Excel. One a worksheet, press Command-F3

That may be the dialog you are looking for.

share|improve this answer

You must log in to answer this question.

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