I am trying to create a Google sheet to consolidate information from different departments.

I have a list of product numbers in column A. I want to compare those values with another column of values on another sheet.

If the value in column A exists in the other column, I want the formula to return an "X".

I have come up with this so far but it is only working for one value in my test list. Please help.

=IF($A:$A=E:E, "X", "")

1 Answer 1


Use match(), like this:

=arrayformula(if(ifna(match(A1:A, Sheet2!E1:E, 0)), "X", iferror(ø)))

Put the formula in row 1 of a free column. See match().


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.