I've a table which has many columns(below is just sample data, in original I've many of those two repeated columns), table look like this :-
I am using this formula to look for Match and get column left to it for Account Name
=INDEX(A3:D3,1,MATCH(G3,A3:E3,0)-1)
I need to drag this, because it's not an arrayformula
Can we make it an arrayformula, or is there any other way to do it, other than this using arrayformula?
Thanks
CodePudding user response：
IF a range contains a value, chose the range to the left of it and join the resulting array.
Minimal reproducible example:
=BYROW(ARRAYFORMULA(IF(E2:E=B2:D,A2:C,)),LAMBDA(r,TEXTJOIN(",",1,r)))
| (A1)Account | Position | Account | Position | Search | Output |
|---|---|---|---|---|---|
| a | Apple | a | Apple2 | Apple | a |
| a | Cherry | a | Cherry2 | Cherry2 | a |
| b | Apple | b | Apple2 | Apple | b |
| c | Apple | c | Orange | Orange | c |
If you just want the first match, use INDEX/MATCH:
=BYROW(ARRAYFORMULA(IF(E2:LR(E:E)=B2:LR(D:D),A2:LR(C:C),)),LAMBDA(r,LR(r)))
where LR is a named function 