Tom
Tom

Reputation: 233

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

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

Upvotes: 5

Views: 12232

Answers (3)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5529

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

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

enter image description here

Upvotes: 0

Max Makhrov
Max Makhrov

Reputation: 18727

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

Upvotes: 5

Tom Sharpe
Tom Sharpe

Reputation: 34400

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"))

enter image description here

Upvotes: 2

Related Questions