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)))