I'm after a formula that extracts entries that are not on either dataset, the filter function gives me a VALUE error, I can only get FILTER to work it out if I have single columns of data. The correct formula would return Jack, Fred, and Milton
Book6 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Group 1 | Group 2 | ||||||
2 | Jack | Ken | Peter | Vic | #VALUE! | |||
3 | Tom | Sally | Tom | Fred | ||||
4 | Peter | Vic | Jane | Milton | ||||
5 | Jane | Sally | ||||||
Sheet1 (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =FILTER($A$2:$B$5,NOT(COUNTIF($D$2:$E$5,$A$2:$B$5))) |