Referencing list in Excel Filter function

user_15482862 0
Viewed 42

My real world example is more complicated but have created the below example to show the problem I am trying to overcome

Excel spreadsheet

So I have a list of names in A1:A10 and another list of names in B1:B3 I need to use the =Filter function to create a list of names excluding the names in B1:B3.

I am able to get around in ths example with the formula =FILTER(A1:A10,(A1:A10<>B1)*(A1:A10<>B2)*(A1:A10<>B3)) however this would be extremly impratical to implement. Am basically looking for somthing that would acomplish A1:10<>B1:B3

Any help would be apperciated.

excel-formula excel
1 Answers

You can use an inner MATCH with NOT and ISNUMBER:

=FILTER(A1:A10,NOT(ISNUMBER(MATCH(A1:A10,B1:B3,0))))

enter image description here

user_92458530
Related