VLOOKUP with merged cell

[Ran78]

I am attaching the file with my problem and the output I need...

[azumi]

For Seller, put this on D3 and ENTERED as ARRAY FORMULAS:
=IFERROR(INDEX(LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13),MATCH(0,INDEX(COUNTIF($D$2:D2,LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13)&""),0,0),0)),"")

For Products, put this on E3 and just ENTERED as REGULAR FORMULAS:
=IF(IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),1)),"")="","",IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),1)),""))&IF(IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),2)),"")="","",", "&IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),2)),""))&IF(IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),3)),"")="","",", "&IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),3)),""))&IF(IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),4)),"")="","",", "&IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),4)),""))&IF(IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),5)),"")="","",", "&IFERROR(INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1)/(LOOKUP(ROW($A$2:$A$13),ROW($A$2:$A$13)/($A$2:$A$13<>""),$A$2:$A$13)=$D3),5)),""))

[FlameRetired]

Hi Ran78. Welcome to the forum.

Another way. For Seller in D3 and filled down.

Please Login or Register  to view this content.

For the Products the attached uses a helper column. The offset row headers in column A cause difficulties similar to merged cells the helper column is one remedy. In C3 and filled down.

Please Login or Register  to view this content.

Then to group the Products this borrowed user defined function by tigeravatar wrapped around the formula at the bottom of this post. It is called Concatall.

'tigeravatar ExcelForum
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
    
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
    
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty
            If Len(DataIndex) > 0 Then
                'Found the item isn't empty, check if user specified bUnique as True
                If bUnique = True Then
                    'bUnique is true, check if the item has been included in the result yet
                    If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                        'Item has not been included in the result, add item to the result
                        strResult = strResult & "||" & DataIndex
                    End If
                Else
                    'bUnique is not true, add item to the result
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
    
    'Output result
    ConcatAll = strResult
    
End Function 

Although it is already installed in the attached for future reference here is how to install VBA code.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the code into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm) or *.xlsb

Note this must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[Bosco]

Another formula option with helper column

1] In "helper" C3, copied down :

=IF(LOOKUP("zz",A$2:A4)=LOOKUP("zz",A$1:A3),B3&" "&C4,B3)

p.s. if you wanted to hide the helper column, C3 >> Custom Cell format, enter ;;; >> copied down

2] In "output" D3, copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$3:A$50)/(A$3:A$50<>""),ROWS($1:1))),"")

3] In "output" E3, copied down :

=IF(D3="","",VLOOKUP(D3,A$3:C$13,3,0))

Regards
Bosco

[FlameRetired]

@ Bosco

I really like your helper column approach. Very cool!