Search code examples
vbscriptlibreofficelibreoffice-calc

VLOOKUP with multiple return values Libre Office VB


I am looking for a VLOOKUP function that returns multiple values in the cell. Sometimes the column you are searching contains more than once the value.

For instance I have a list of products to import in Woocommerce, those products have variables and variations. I want to fill attributes cells of Variable with all its variation's attributes as a list. Then I search for ID in parent column and return the list of attributes.

enter image description here


Solution

  • Here is what I found.

    'E2 = searchCell = SKU
    '3 = columNumWhereToSearch = Parent
    '6 = columnNumWhatToGet = Attribute
    
    Function MULTIPLE_VLOOKUP(searchCell, columNumWhereToSearch, columnNumWhatToGet) As String
        Dim Doc As Object
        Dim Sheet As Object
        Dim Cell As Object   
         
        Doc = ThisComponent
        Sheet = Doc.Sheets(0)
    
        Dim AttributeListString As String
        AttributeListString = ""
        
        'Set the number of row to search
        For I = 1 To 150
            CellToSearch = Sheet.getCellByPosition(columNumWhereToSearch, I)
            If CellToSearch.String = searchCell then
                CellToGetAndReturn = Sheet.getCellByPosition(columnNumWhatToGet, I).String
                If Len(CellToGetAndReturn) > 0 And CellToGetAndReturn <> "Err:522" And CellToGetAndReturn <> "Err:508" And CellToGetAndReturn <> "#NAME?"  And CellToGetAndReturn <> "#NULL!" And CellToGetAndReturn <> "#VALUE!" And CellToGetAndReturn <> "#REF!" then
                    If Len(AttributeListString ) > 0 then
                        AttributeListString = AttributeListString + ","
                    End If
                    AttributeListString = AttributeListString + CellToGetAndReturn
                End If
            End If
        Next I
        multiple_vlookup = AttributeListString
    End Function
    

    Use it like =MULTIPLE_VLOOKUP(E2;4;7)