CODEWITHSUNDEEP
vbscriptlibreofficelibreoffice-calc
73k05
73k05

Reputation: 169

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

Upvotes: 1

Views: 841

Answers (1)

73k05
73k05

Reputation: 169

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)

Upvotes: 1

Related Questions