Excel: SuperVlookup

Sometimes you have multiple values to vlookup all separated by a particular delimiter. The following function performs this multiple lookup for you, and returns the values separated by line breaks. For example:

Below is a function macro that performs this function:

Function SuperVlookup(ByVal Lookup_value As String, ByVal Table_array As Range, ByVal Col_index_num As Integer, ByVal sDelimiter As String) As Variant

' Looks up multiple valuess separated by a defined delimiter.

' Edward Chan 2017-08-15

    Dim vSplit As Variant

    vSplit = VBA.Split(Lookup_value, sDelimiter)

    On Error Resume Next

    For Each Substring In vSplit

        lookupvalue = "TBD"

        Substring = WorksheetFunction.Clean(Substring)

        Substring = WorksheetFunction.Trim(Substring)

        lookupvalue = WorksheetFunction.VLookup(Substring, Table_array, Col_index_num, False)

        If lookupvalue <> "" Then SuperVlookup = SuperVlookup & lookupvalue & Chr(10)

    Next

    SuperVlookup = Left(SuperVlookup, Len(SuperVlookup) - 1)

End Function

Office 365 equivalentS

Office 365 supports array formulas automatically.

There are two ways to do this, one using VLOOKUP and the other using FILTER/BYROW .

Vlookup (preferred)

Vlookup now works with arrays - it will now look up each cell in turn and return an array. The advantage of this formula is that it will return all the values in exactly the same order as the lookup. This is of course the preferred method, because it retains a one-to-one ordering.

=TEXTJOIN(",",TRUE,VLOOKUP(TEXTSPLIT(compoundCell,","),database,Col_index_num,FALSE))

compoundCell : The cell with the compound text to be split and then looked up.

database : Database range (first column needs to be index)

Col_index_num : The number of the column to return, where 1 is the index column.

Filter and ByRow

You can perform a similar thing using Filter and ByRow, but the caveat is that the values will be returned in the order that the database is arranged. So the results won't be in the same order as the lookup column.

=TEXTJOIN(CHAR(10),TRUE,FILTER(DatabaseValue, BYROW(TEXTSPLIT(C9,CHAR(10))=DatabaseKey,LAMBDA(row,OR(row)))))

Breaking this formula up:

=TEXTJOIN(                   Join these values

    ",",                     that are separated by commas:

    TRUE,                    Ignore blanks

    FILTER(                  Return the

        DatabaseValue,         values corresponding to

        BYROW(                 each of the following

            TEXTSPLIT(               

                C9,              strings separated by 

                ","              commas 

            ) 

            =                    where they are the same

            DatabaseKey,         as these keys

            LAMBDA(

                array,

                OR(array)

            )

        )

    )

)

Google sheets equivalent

Google Sheets supports array formulas, so similar to O365, there's an easy way without macros.

=ArrayFormula(join(CHAR(10),vlookup(split(compoundCell ,CHAR(10)),database,Col_index_num,FALSE)))