SOLVED

# Looking for help on what Function to use

Occasional Contributor

# Looking for help on what Function to use

I have two columns of names in excel. Column 1 has all names , column 2 has a majority of names. What function can I use to see which names in column 1 don’t appear in column 2? Thank you for any guidance!
11 Replies
Best Response confirmed by John_Murphy (Occasional Contributor)
Solution

# Re: Looking for help on what Function to use

Let's say the names are in A2:A100 and D2:D50.

In B2 (or another cell in row 2), enter the formula

=ISERROR(MATCH(A2,\$D\$2:\$D\$50))

This will return TRUE if the value of A2 does not occur in B2:B50, FALSE otherwise.

Fill down to row 100.

You can also use this formula in a conditional formatting rule for A2:A100 of type 'Use a formula to determine which cells to format' to highlight the non-matching names.

# Betreff: Looking for help on what Function to use

Array formula:

{=INDEX(A:A;SMALL(IF(COUNTIF(B\$1:B\$99;A\$1:A\$99)=0;ROW(\$1:\$99));ROW()))}

and copy it down

* Array Formula is a special type of formula that must be entered by pressing Ctrl+Shift+Enter.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

# Re: Looking for help on what Function to use

Taking @Hans Vogelaar sample, on modern Excel that could be

``=FILTER(A2:A100,COUNTIF(D2:D50,A2:A100)=0)``

assuming you have no blank cells in ranges, otherwise it'll be bit more complex.

# Re: Looking for help on what Function to use

Thank you. Love this site. So amazing

# Re: Looking for help on what Function to use

Hi there,

You may use the following formula to compare two lists (Tables). Sample table is attached:

1 - COUNTIF(\$A\$2:\$A\$17,B2) &  COUNTIF(\$B\$2:\$B\$17,A2)

2 - =IF(COUNTIF(\$A\$2:\$A\$17,B2)=0,ROW(B2)) & =IF(COUNTIF(\$B\$2:\$B\$17,A2)=0,ROW(A2))

3 - =IFERROR(INDEX(\$B\$2:\$B\$17,SMALL(\$I\$2:\$I\$19,L2)-1),"") & =IFERROR(INDEX(\$A\$2:\$A\$17,SMALL(\$J\$2:\$J\$19,L2)-1),"")

4 - =IFERROR(INDEX(\$B\$2:\$B\$17,SMALL(IF(COUNTIF(\$A\$2:\$A\$17,B2:B17)=0,ROW(B2:B17)), L2)-1),"") & =IFERROR(INDEX(\$A\$2:\$A\$17,SMALL(IF(COUNTIF(\$B\$2:\$B\$17,A2:A17)=0,ROW(A2:A17)), L2)-1),"") (Ctrl+Shift+Enter)

At the same time, you can do it using Conditional Formatting:

Picture is attached

Несколько способов сравнить два списка в Excel между собой и быстро найти разницу между ними (цветом или формулами). Подробная статья и пример для скачивания...

# Re: Looking for help on what Function to use

Thank you for all your help. The functions worked when I just created two quick test lists however when I wanted to use the function on my actual data it did not. The problem I am having is each column is filtered. For example 100 names in column A, I applied a filter so that only 30 of the 100 names show on the spreadsheet. When I go to compare the two columns using the functions everyone suggested it is somehow grabbing the entire unfiltered list and not just what’s showing. Instead of 1-30 being used is in the functions its automatically selecting basically 1-100 bc the last person may have been row 100 on the unfiltered list. How do I get my functions to use just what’s currently showing and not the unfiltered results. Thank you!

# Re: Looking for help on what Function to use

Let;s say that the first list is on Sheet1 in A2:A100, and the second list on Sheet2 in A2:A50.

Enter the following array formula in B2 on Sheet2, confirmed with Ctrl+Shift+Enter (you may not need this in Excel 365):

=ISERROR(MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!\$A\$2:\$A\$100,ROW(Sheet1!\$A\$2:\$A\$100)-ROW(Sheet1!\$A\$2),0,1))>0,IF(Sheet1!\$A\$2:\$A\$100=A2,1)),0))

Fill down to B50.

# Re: Looking for help on what Function to use

Thanks Hans but the issue I’m having is when I’m selecting A2-A100 it’s selected all values A2-A350 bc the last name was row 350 on the unfiltered original spreadsheet. How can I copy the list so it removes the original location on initial unfiltered spreadsheet or modify the function

# Re: Looking for help on what Function to use

I'm afraid I don't understand. Can you explain in detail what exactly you want to accomplish?