# How to use VLOOKUP to return Multiple Values from Single Variable

#### `Package Payments` Sheet

B C
1 Users Total Numbers
2 User 1 2
3 User 2 4
4 User 3 1
5 User 3 2
6 User 3 4
7 User 3 7

#### Desired Results

A B
1 Users Total Numbers
2 User 1 2
3 User 2 4
4 User 3 1,2,4,7

Here's my current formula:

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

I'm using `VLOOKUP` to search for a variable, and return values related to that variable in a single cell.

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

I had thought this code would reiterate and produce the results for all users. I also want to avoid using query as I want to repeat the code throughout the sheet.

## 4 Answers

`=ArrayFormula(TEXTJOIN(",",1,IF(D1=\$A\$1:\$A\$6,\$B\$1:\$B\$6,"")))` :

Even though this formula contains an ArrayFormula function it still needs to be copied down to E2, E3, etc.

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
)
)
}
)
``````
• Hi there! I tried the first code you sent: — =textjoin( ", ", true, filter('Package Payments'!C\$2:C, 'Package Payments'!B\$2:B = B2) ) — It works, but it's only fetching values from a single user. I tried extending the search for the variable by using : 'Package Payments'!B\$2:B = B2:B However, it is not working, it is only displaying one value. I want it to display values related to the proeperty. I appreciate you giving the alternative formula. However, I wanted to collate the results to a single row. Feb 26, 2022 at 7:58
• The second formula gets all the results for a username in the same row. If that is not what you wanted, consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Feb 26, 2022 at 9:08
• Hi Bud! Thanks, here's a link for the Google Sheet File: docs.google.com/spreadsheets/d/… If you'll check on the Package Payment Tab. There are two columns there labelled. Package Payments, and Client Payments. I want Package Payments to have the same Result as Client Payments. Feb 28, 2022 at 0:08
• Edited the answer. Your sample spreadsheet has more than 30,000 blank rows on most tabs. Delete those superfluous blank rows before applying the array formula. Feb 28, 2022 at 6:58

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))," ",","))})
``````

## Formula

``````=BYROW(TOCOL(UNIQUE(A2:A), 1), LAMBDA(r,
{r, JOIN(",", FILTER(B2:B, A2:A=r))}))
``````

## Explanation

1. UNIQUE and TOCOL are combined to create unique list of users without a blank value.
2. BYROW passes each row one-by-one into a LAMBDA function
3. LAMBDA stores the current row's value in the variable `r`
4. LAMBDA's formula creates a 2-column array:
• The first column is the value stored in `r`
• The second column's is returned by filtering the Numbers column B2:B for all rows where the Users column A2:A = `r`, then JOIN is used to concatenate the results.