Home > Mobile >  Finding the first value in a row and getting the address using ArrayFormula
Finding the first value in a row and getting the address using ArrayFormula

Time:10-29

enter image description here

in this example table, I want to get the first "failed" or "passed" from result 1, 2, and 3 column. I already made a formula to get the first mentioned value using:

=IF(C2=C2,CELL("address",INDEX(D2:F2,MATCH(C2,D2:F2,0))),)

which works fine (the column result is from that formula)

But when I'm trying to use ArrayFormula on the formula, it only shows the first row value only. my ArrayFormula is like this:

=ArrayFormula(IF(C2:C4=C2:C4,CELL("address",INDEX(D2:F2,MATCH(C2:C4,D2:F2,0))),))

enter image description here

i think its because the INDEX and MATCH doesn't update as it goes down, any idea how to fix this?

Sheets link: enter image description here

Or alternatively you can use a formula similar to your original one with Byrow:

=ArrayFormula(if(C2:C="",,byrow(C2:F,lambda(r,CELL("address",INDEX(r,1 MATCH(index(r,,1),index(r,,2):index(r,,columns(r),0))))))))
  • Related