Help Using Excel Function

[John_Murphy]

I posted an earlier question looking for a formula to use to find which names that are in column 2 but not column 1. The problem I ran into is that column 1 and column 2 are filtered columns. I have applied a filter so what is showing is let’s say 30 names but the original spreadsheet has 100 names. When using the functions suggested they continued to use the entire unfiltered spreadsheet instead of what I was seeing on my filtered view. What can I do thanks in advance!!!

[#2]

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