0

I have this formula working in Google Sheets:

=if(B3="",0,IF(ISERROR(VLOOKUP(E3,E4:E,1,FALSE)),1,0))

The logic is pretty straight forward: if B3 is NOT NULL and there is no duplicate of E3 in range E4:E, write 1 else write 0

I tried to convert it to ARRAYFORMULA, coz it should be applied to all columns in E as and when the number of rows increase (via form submission), by doing this:

=ARRAYFORMULA(if(B3="",0,IF(ISERROR(VLOOKUP(E3:E,E4:E,1,FALSE)),1,0)))

But, it wrote 0 to all columns of E.

The reason here is that, the VLOOKUP should look in the E column range excluding the current row. I'm not sure how to achieve this.

Here is the Google Spreadsheet (please refer to Sheet2)

Can someone please correct my ARRAYFORMULA? Thank you.

Prashanth JC
  • 537
  • 1
  • 6
  • 20

1 Answers1

1

You can't offset range used in ArrayFormula, so your original formula cannot be converted into ArrayFormula and remain result. But you may use this workaround:

=ArrayFormula(IFERROR(--(VLOOKUP(OFFSET(E3,,,COUNTA(A3:A)),QUERY({ROW(INDIRECT("A1:A"&COUNTA(A3:A))), OFFSET(E3,,,COUNTA(A3:A))},"select Col2, max(Col1) where Col2 <> '' group by Col2 label Col2 '', max(Col1) ''"),2,0)=ROW(INDIRECT("A1:A"&COUNTA(A3:A)))),0))

I used row function in this formula to compare it with maximum row when certain value appears.

Your sample file

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81