# If values in a range appear in another range, remove from list in another cell

I've been stumped on this googlesheets problem and can't think of the best way to do this or I'm overthinking this.

Essentially if I have a list of values, I'd like to compare it to another list of values. And if they have the same values at any point, I'd like it to remove it from an updated list that's outputted into a new cell.

Eg.

In my example, I have a list of fruits in column A, a different list of fruits in column B, and the difference between column A and column B is what I'd like to get in column C. Thanks for the help!

Edit: Ah, I'ved tried using the unique function, but it would give me the unique values of both the lists. I've also tried using the filter function, but I'm not exactly sure how the criterion for the filter would work with a list of values

• Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask.
– Rubén
Sep 13, 2019 at 23:15
• Ah, I'ved tried using the unique function, but it would give me the unique values of both the lists. I've also tried using the filter function, but I'm not exactly sure how the criterion for the filter would work with a list of values. Sep 13, 2019 at 23:26
• Please edit your question to add all the relevant details to it. On this site, comments are only for giving feedback/suggestion to improve questions/answer and giving transitional information (they could be deleted).
– Rubén
Sep 13, 2019 at 23:30

## 1 Answer

Here's a way to achieve this

``````=FILTER(B1:B10,
iferror(ArrayFormula(
VLOOKUP(B1:B10,\$A\$1:\$A\$10,1,false))
,true))
``````

EDIT : If you have a lot of values this is maybe a better solution :

``````=FILTER(B1:B10,ArrayFormula(COUNTIF(\$A\$1:\$A\$10,B1:B10)-1))
``````

Breakdown of the formula `VLOOKUP`:

`Vlookup` to see if there's a match between values in `B` and `A`. It returns the value when true, and an error when false.
`Iferror` to replace the error with `true`.
`Filter` to keep only the relevant answers (when there's a `true` statement).

Formula `COUNTIF` :

`Countif` there's the value `B` in `A` : return `0` = (`False`) or `1` = (`TRUE`).
`-1` : now the `False` values are `true` and `true` values are `false`.
/!\ this doesn't work when column A contains duplicate : use `UNIQUE` to futur-proof the formula.

• This formula works perfectly! I even added a unique function to it incase I get duplicates in the B column! Thank you! =UNIQUE(FILTER(B1:B10,iferror(ArrayFormula( VLOOKUP(B1:B10,\$A\$1:\$A\$10,1,false)) ,true))) Sep 14, 2019 at 12:01