How to Use VLOOKUP to return Multiple Values from Single Variable

Question

I'm trying to create another formula for my project.

I'm using VLOOKUP to search for a variable, and return values related to that variable on a single Cell.

Here is what I'm working With

Sheet A

A         |       B
Users       Total Number
User 1           
User 2
User 3           1,2,4,7
Sheet B

A         |       B
Users       Total Number
User 1           2
User 2           4
User 3           1
User 3           2
User 3           4
User 3           7

I want to collect the numbers that are related per User on a single cell. Here's my current formula used:

=ARRAYFORMULA({"Client Payments"; JOIN(",", VLOOKUP(B2, 'Package Payments'!B2:E, 2, FALSE))})

I thought this code would reiterate itself and produce the result on Sheet A, User 3's Row. Also, I want to avoid using query as I want to repeat the code all-throughout the sheet.

Any recommendations are welcomed.

Answer 1

Use filter(), like this:

=textjoin( ", ", true, filter('Package Payments'!C$2:C, 'Package Payments'!B$2:B = B2) )

Alternatively, try this pattern to get a multi-column table that shows all numbers for all usernames:

=filter( 
  query( 
    { 
      'Package Payments'!B2:C, 
      countifs('Package Payments'!B2:B, 'Package Payments'!B2:B, row('Package Payments'!B2:B), "<=" & row('Package Payments'!B2:B)) 
    }, 
    "select Col1, max(Col2) 
     where Col1 is not null 
     group by Col1 
     pivot Col3", 
    0 
  ), 
  sequence(counta(unique('Package Payments'!B2:B)) + 1) > 1 
)

To place those results in your sample spreadsheet, looking results up by client name, put this formula in cell 'Package Clients'!J1:

=arrayformula( 
  { 
    "Package Payments"; 
    transpose( 
      query( 
        transpose( 
          iferror( 
            vlookup( 
              B2:B, 
              filter( 
                query( 
                  { 
                    'Package Payments'!B2:C, 
                    countifs('Package Payments'!B2:B, 'Package Payments'!B2:B, row('Package Payments'!B2:B), "<=" & row('Package Payments'!B2:B)) 
                  }, 
                  "select Col1, max(Col2) 
                   where Col1 is not null 
                   group by Col1 
                   pivot Col3", 
                  0 
               ), 
                sequence(counta(unique('Package Payments'!B2:B)) + 1) > 1 
              ), 
              sequence(1, 99, 2), 
              false 
            )  
          ) 
        ), 
        "", 
        9^9  
      ) 
    ) 
  } 
)

Answer 2

Here is my proposed solution:

Formula Used (Improved - to include open range as well):

=ArrayFormula({UNIQUE(FILTER(A1:A,A1:A<>"")),transpose(regexreplace(trim(query(transpose(REGEXEXTRACT(query(A1:A&"~"&B1:B,,3^6),REGEXREPLACE(query(A1:A&"~"&B1:B,,3^6),"("&UNIQUE(FILTER(A1:A,A1:A<>""))&"~)(\d+)","$1($2)"))),,4^6))," ",","))})