Forum Discussion

Copper Contributor

Oct 02, 2020

Solved

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 guid...

excel

HansVogelaar
Oct 02, 2020
John_Murphy

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.

HansVogelaar

MVP

Oct 14, 2020

John_Murphy

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.

John_Murphy

Copper Contributor

Oct 14, 2020

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

HansVogelaar
MVP
Oct 14, 2020
John_Murphy

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