in col A I have a list of email addresses
in col B I want to catch duplicates, so if an email address in A appeared before I get a trigger in B.
I'm using this formula which works great:
=if(isna(match(a3,$A$2:A2,0)),"New","Duplicate")
note that as I drag this formula, $A$2 stays so the range grows (e.g. for cell B51 the range will be from $A$2:A50)
My problem is that since column A updates automatically (e.g. new email addresses are added) I want column B to update automatically as well. I tried using Arrayformula but can't figure it out :(
I tried both:
=arrayformula(if(isna(match(A3:A,$A$2:A2,0)),"New","Duplicate"))
and
=arrayformula(if(isna(match(A3:A,$A$2:A2:A,0)),"New","Duplicate"))
but they don't work.
here's a spreadsheet with an example and my (failing) attempts to solve it https://docs.google.com/spreadsheets/d/1N3pFPnT452FmWa9w8EkYpIq-ZnivjoCzt5ORrNEKgLQ/edit#gid=0
Please, try:
=ArrayFormula(IFERROR(if(VLOOKUP(A2:A,{A2:A,ROW(A2:A)},2,)=ROW(A2:A), "New", "Duplicate")))
If matching row = current row → "New", else → "Duplicate".
I used vlookup because it may be used with ArrayFormula