# VLOOKUP to return multiple rows and columns into one cell?

#### gmooney

##### Active Member
I have a large table of about 1500 rows that have various facts that I need to return into a single vlookup formula. I need to bring in multiple rows into the same cell based on the fact that the rows can have duplicate dates and geographies.

Below is a snapshot of the data.....

Assume that I am adding the formula into a different sheet in cell C1 and that "Sales Start Date" is in cell A1 and "Retailer" is in cell B1 I need the results of "Product", "Size", and "Unit Cost"

So my answer in cell C1 should be

STR (12-16oz) - \$2.50 & BLU (12-16oz) - \$2.50 & ORG STR (16oz) - \$3.00

 Sales Start Date Sales End Date Retailer Parent Retailer Market Product Brand Manufacturer Size Ad Price Reg Price Offer Unit Price 8/20/2014 8/26/2014 Kroger ATL Kroger Atlanta, GA STR Fresh Grocery Fresh Grocery 12-16OZ 2/\$5.00 \$2.50 8/20/2014 8/26/2014 Kroger ATL Kroger Atlanta, GA BLU Fresh Grocery Fresh Grocery 12-16OZ 2/\$5.00 \$2.50 8/20/2014 8/26/2014 Kroger ATL Kroger Atlanta, GA ORG STR Fresh Grocery Fresh Grocery 16OZ 2/\$6.00 \$3.00 8/13/2014 8/19/2014 Kroger ATL Kroger Atlanta, GA BLU Fresh Grocery Fresh Grocery 1PT \$1.99 \$1.99 8/13/2014 8/19/2014 Kroger ATL Kroger Atlanta, GA STR Fresh Grocery Fresh Grocery 16OZ 2/\$5.00 \$2.50 8/13/2014 8/19/2014 Kroger ATL Kroger Atlanta, GA ORG STR Fresh Grocery Fresh Grocery 16OZ 2/\$7.00 \$3.50 8/6/2014 8/12/2014 Kroger ATL Kroger Atlanta, GA STR Fresh Grocery Fresh Grocery 16OZ \$1.99 \$1.99 8/6/2014 8/12/2014 Kroger ATL Kroger Atlanta, GA BLU Fresh Grocery Fresh Grocery 1PT 2/\$3.00 \$1.50

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
For clarity, let's assume that A1:M9 contains the table, O2 contains the start date, and P2 contains the retailer. First, place the following code in a standard module (Alt+F11 > Insert > Module > Copy/Paste code > Alt+Q)...

Code:
``````Option Explicit
Function AConcat(a As Variant, Optional Sep As String = "") As String
' Harlan Grove, Mar 2002
Dim Y As Variant

If TypeOf a Is Range Then
For Each Y In a.Cells
AConcat = AConcat & Y.Value & Sep
Next Y
ElseIf IsArray(a) Then
For Each Y In a
AConcat = AConcat & Y & Sep
Next Y
Else
AConcat = AConcat & a & Sep
End If

AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

End Function``````

Then try the following worksheet formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

Code:
``=SUBSTITUTE(AConcat(IF(\$A\$2:\$A\$9=O2,IF(\$C\$2:\$C\$9=P2," & "&\$F\$2:\$F\$9&" ("&\$I\$2:\$I\$9&") - "&TEXT(\$M\$2:\$M\$9,"\$#,##0.00"),""),""))," & ","",1)``

Hope this helps!

Thank you..this worked perfectly....now I have one more thing that I need to do with this.

In another cell I need to bring in the day of the week that the Start Dates are. For example most of the time the retailers will have the same start day of the week for all of their Ads but occasionally they will change them to a different day of the week.

In my example I want for it to simply give me a list of any day of the week that the start dates align with. If every AD starts on Wednesday then I want only Wednesday returned...If most ADs broke on Wednesday but earlier in the Year they were breaking on Sunday I want my formula to tell me Wednesday and Sunday and the start date that the Ads changed from Sunday to Wednesday.

Any help is greatly appreaciated.

Replies
5
Views
876
Replies
3
Views
1K
Replies
0
Views
631
Replies
6
Views
918
Replies
1
Views
502

1,219,569
Messages
6,149,035
Members
450,852
Latest member
dmotz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back