-2

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 :-

enter image description here

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 enter image description here

Can we make it an arrayformula, or is there any other way to do it, other than this using arrayformula?

Thanks

vector
  • 1,032
  • 1
  • 4
  • 16

2 Answers2

2

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 here

TheMaster
  • 45,448
  • 6
  • 62
  • 85
0

Try following formula-

=BYROW(G2:G9,LAMBDA(x,INDEX(FILTER(A2:A9,D2:D9=x,ROW(D2:D9)>=ROW(x)),1)))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • In your language `=BYROW(G2:G9,LAMBDA(x,INDEX(FILTER(A2:A9,D2:D9=x,ZEILE(D2:D9)>=ZEILE(x)),1)))`. – Harun24hr Sep 27 '22 at 10:58
  • Hi, Thanks for the answer what if I have more column, in my original sheet i've 80+ of those repeated column, , I added two more Account Name in sheet, and this formula breaks I think, thank you once again – vector Sep 27 '22 at 11:03
  • Its the Error Calculation limit was reached while trying to compute this formula. driving me crazy See [this erorr](https://i.imgur.com/dLLsAUg.png) Maybe try `IF(ROW(range)<=ROW(range),` – Osm Sep 27 '22 at 11:03
  • Ya, I've been banging my head too, formula is simple when you're ready to drag it down, but with Array, i can't think of any possible solution – vector Sep 27 '22 at 11:07
  • No i ment the `LAMBDA` formula throws limitation error, if you have limited rows your ok, if you have a lot of rows its not a good solution, hopefully @Harun24hr will update the anser with option 2, [see this comment](https://stackoverflow.com/questions/73866211/converting-horizontal-search-in-match-to-arrayformula#comment130429387_73866479) – Osm Sep 27 '22 at 11:12
  • I am new to Lambda Function :P – vector Sep 27 '22 at 11:17