Array Formula with indirect not working

0
votes

Just cannot figure this out.

Test Data: B2,3,4 = "a","b","b"; D2,3,4 = "c","d","d"

Results should be "", "CHOICE", "DUP"

First I tried this in cell E1:

=ArrayFormula(if(row(A:A)=1,"Header",if((B2:B & D2:D)=(indirect("B"&row(B2:B)-1)&indirect("D"&row(D2:D)-1)),"DUP",if((B2:B & D2:D)=(indirect("B"&row(B2:B)+1)&indirect("D"&row(D2:D)+1)),"CHOICE",""))))

That brought back an error that B0 is an invalid cell reference.

Next I tried the below formula in cell E2:

=ArrayFormula(if((B2:B & D2:D)=(indirect("B"&row(B2:B)-1)&indirect("D"&row(D2:D)-1)),"DUP",if((B2:B & D2:D)=(indirect("B"&row(B2:B)+1)&indirect("D"&row(D2:D)+1)),"CHOICE","")))

This one produces results "CHOICE","","" which I just cannot understand.

Any thoughts?

1
arraysgoogle-sheetsarray-formulas
Concat(b3,d3)=concat(b4,d4), so for e3, concat(b3,d3)=concat(b3+1,d3+1)= choice. And for e4,concat(b4,d4)=concat(b4-1,d4-1)=dup – sandeaj
The formula works if you take out the array part – sandeaj
Actually, it should be blank, choice, dup because concat(b2,d2) doesn’t match the row above it or the row below it. – sandeaj

1 Answers

0
votes

The reason is that indirect doesn't work as an array formula. Try this

=concatenate(B2:B4)

Now try this

=ArrayFormula(concatenate(indirect("B"&row(B2:B4))))

What do you get?

So in your formula

row(B2:B)-1

just gives you row 1

and

row(B2:B)+1

just gives you row 3.

You can use this approach instead of indirect

=ArrayFormula(if((B2:B & D2:D)=B1:index(B:B,rows(B:B)-1)&D1:index(D:D,rows(D:D)-1),"DUP",if((B2:B & D2:D)=B3:index(B:B,rows(B:B)-1)&D3:index(D:D,rows(D:D)-1),"CHOICE","")))

It works but needs a bit more refinement - if the sheet has (say) 1000 rows, because of the offsetting you should only expand the array to 998 rows.

This is better

=ArrayFormula(if((B2:index(B:B,rows(B:B)-1) & D2:index(D:D,rows(D:D)-1))=B1:index(B:B,rows(B:B)-2)&D1:index(D:D,rows(D:D)-2),"DUP",if((B2:index(B:B,rows(B:B)-1) & D2:index(D:D,rows(D:D)-1))=B3:index(B:B,rows(B:B))&D3:index(D:D,rows(D:D)),"CHOICE","")))

Or you can use offset instead of index

...offset(B2,0,0,rows(B:B)-2,1) ....

or replace your indirect with

...indirect("B2:B"&rows(B:B)-1)...