Forum Discussion
Solved
How to compare two ranges of names?
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
=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.
=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.
