SOLVED

How to compare two ranges of names?

Frequent Contributor

if I have two ranges of name

Range 1 F11:F22
Range 2 H11:H15
I want to compare these two ranges such that the result should appear only the name of range H11:H15 which are not existing in range F11:F22. The result should be as shown in range J11:J13

the length of ranges 1 and 2 is variable and it is not fixed.

I need to use the required function, which compares the two ranges, with the filter function.

See the attached file, please. Many thanks

1 Reply
best response confirmed by ajl_ahmed (Frequent Contributor)
Solution

@ajl_ahmed 

=FILTER(H11:H15,ISERROR(MATCH(H11:H15,F11:F22,0)),"")

 

or

 

=FILTER(H11:H15,COUNTIF(F11:F22,H11:H15)=0,"")

 

Remark: it will also return Name12, since the range on the left has Name 12 and the one on the right has Name12 without a space.