1
votes

I have two columns on two different sheets with a long list of values; I'm trying to compare these two columns to check if an entry in the first one exists in the second one. In the first column there are also blank cells.

I tried different formulas but all of them is returning a value for blank cells, instead of a blank cell.

These are the formulas I tried:

=arrayformula(iferror(if(match(A1:A,AnotherSheet!A1:A,0),"yes"),"no"))

=query(arrayformula(iferror(if(match(A1:A,AnotherSheet!A1:A,0),"yes"),"no")), "Select * where Col1<>''")

=query(arrayformula(iferror(if(match(A1:A,AnotherSheet!A1:A,0),"yes"),"no")), "Select * where Col1 is not empty")

All of them is returning "no" for blank cells in the first column, I would like to have a blank cell instead. Thank you

1
I found a formula that works but only for "yes" value: =arrayformula(iferror(if(match(A1:A,AnotherSheet!A1:A,0),"yes","no"))); how to make it return the "no" value? - AlxMrx

1 Answers

0
votes
=ARRAYFORMULA(IF(LEN(A1:A), IFERROR(IF(MATCH(A1:A, AnotherSheet!A1:A, 0), "yes"), "no"), ))