How do I use Arrayformula with a Match function that uses a dynamic range in Google Sheets?

Question

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

Answer 1

You can also do a countif that looks at everything above the current row:

=IF(countif($A$1:A2,A2)>1,"DUPLICATE","NEW")

Answer 2

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

Answer 3

You can do it using Match to see if the first match for the current email address is before the current row of the array

=arrayformula(if(match(A2:index(A2:A,COUNTA(A2:A)),A2:index(A2:A,COUNTA(A2:A)),0)<row(A2:index(A2:A,COUNTA(A2:A)))-1,"Duplicate","New"))