Need formula to show/bring in multiple values from col B using col A using index/match formula

Question

I have Col A with the same value and Col B with diff values. I need a formula that returns each different value in Col B using Col A as the lookup column. Was using index/match but it only brings up the first match in this case Toy1 and ignores any and all values in Col B after the first match. Thank you!

enter image description here

Tried index/match but only one result was returned, was hoping for an error or multiple values to return.

Answer 1

Here / screenshot(s) refer:

Method 1*

• assumes Office 365 compatibility (version Excel)

  =FILTER(Table1[̿̿ ̿̿ ̿̿ ̿’̿’\̵͇̿̿\З= ( ▀ ͜͞ʖ▀) =Ε/̵͇̿̿/’̿’̿ ̿ ̿̿ ̿̿ ̿̿],--(Table1[col A]=G4))

---

Method 2* reverse match but can be modified for non-Office 365 users (why others don't use latest / greatest - still bewildering TBH).. anyways:

=LET(x_,IFERROR(SEQUENCE(ROWS(Table1[col A]))*MATCH(Table1[col A],G12,0),""),y_,INDEX(Table1[̿̿ ̿̿ ̿̿ ̿’̿’\̵͇̿̿\З= ( ▀ ͜͞ʖ▀) =Ε/̵͇̿̿/’̿’̿ ̿ ̿̿ ̿̿ ̿̿],x_),z_,FILTER(y_,ISNUMBER(x_)),z_)

---

Method 3:

If you've gotten this far - nothing better to do - it uses a combination of if./text join and split to be fancy schmancy:

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",1, IF(Table1[col A]=G12,Table1[̿̿ ̿̿ ̿̿ ̿’̿’\̵͇̿̿\З= ( ▀ ͜͞ʖ▀) =Ε/̵͇̿̿/’̿’̿ ̿ ̿̿ ̿̿ ̿̿],"")),","))

---

Finally - like I didn't have anything better to do- I conclude with this wtf method - go figure yourself pls (similar to one of the above - you only asked for 1 method - so this is a bonus on bonus on yep, bonus bra)

=LET(x_,SORT(IF(Table1[col A]=G27,Table1[̿̿ ̿̿ ̿̿ ̿’̿’\̵͇̿̿\З= ( ▀ ͜͞ʖ▀) =Ε/̵͇̿̿/’̿’̿ ̿ ̿̿ ̿̿ ̿̿],""),,-1),FILTER(x_,--(x_<>"")))

---

If these don't work for you (assuming 365 at the very least) - then not sure what will / although numerous ways to skin a cat.

Vote/like/subscribe as you deem fit for the benefit of future users who will invariably find themselves asking the self-same Q.
Best of luck, ta, J

---