Multiple Vlookup values and placed in one cell

[cjc155]

Hi everyone,

I have found a few postings on looking up multiple values using a vlookup but my request is a bit different.

For this example, let's say I am selling something called "Seasonal Gift Basket." In an area of my worksheet I have all the individual SKU's that make up the current Gift Basket, i.e. Apples, Pears, Plums. To be exact, in sell A1 I have the words "Seasonal Gift Basket" or SGB for short and in sells B1, B2, B3 I have Apples Pears Plums respectively. This is soft of my item list.

Now, below in the spreadsheet I have an order tracker where A20 is Seasonal Gift Basket (or SGB). In cell B20, I would like to lookup the "item list" (vlookup(a1,B1:b3...) and put the values into this sell separated by carriage returns. I want to do this as the "items" within the Seasonal Gift Basket will change throughout the year as well as I don't want to have a bunch of rows.

I have seen this down using array counts, etc, but I would prefer not to have the items in my "order tracker" in separate cells. Is there a good way to do this? Thanks for your help

 

[cjc155]

Hi..

so I found this code

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & " " & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = result
End Function


That works fine but was wondering if anyone knew how I could add a carriage return to my results. Thanks...should have done more searching before I posted the question.

 

[cjc155]

Hi,

So I think I figured it out. Any issue with doing the following?

Function MYVVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)


Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & vbLf & r.Offset(0, indexcol - 1)
End If
Next r
MYVVLOOKUP = result
End Function

 

[cjc155]

nope, that doesn't work...any ideas?