Convert an if & vlookup combination formula to arrayformula - Google Sheets

0
votes

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.

1
excelgoogle-sheets

1 Answers

1
votes

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