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

Why don't you remove duplicates when you update the email list? — Cooper
It's for reporting so I need both, how many New emails and how many are repeating. — Tom

Max Makhrov Max Makhrov · Accepted Answer · 2017-07-28T06:39:34

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

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

3 Answers