Lookup multiple values separated by a comma in a cell and return multiple values separated by a comma

Question

if anyone can please assist with this

Let's call this sheet 1

I want to perform a lookup here where I can bring in the values from Column B in Sheet 2 into Column B in sheet 1. The multiple values returned should be separated by commas as well. I have tried using the textjoin formula here but where it screws up is for example it will count A12 as A1 and A12 thereby returning 2 values for one value.

I can try my hand at a VBA code but haven't had much positive results.

Answer 1

You could use FILTERXML() to split your codes per comma into an array of values we can process through VLOOKUP(). Then concatenate the returned array back into a comma seperated string using TEXTJOIN():

Formula in E2:

=TEXTJOIN(",",,VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(D2,",","</s><s>")&"</s></t>","//s"),A$2:B$6,2,0))

Where:

• "<t><s>"&SUBSTITUTE(D2,",","</s><s>")&"</s></t>" - Creates an valid XML-string, replacing the comma with start/end tags.
• "//s" - Valid XPATH to return all s-nodes.

Note: If you are using Excel 2019, this is supposed to be entered through CSE. If you are interested in more in-depth explainations on why this works, I'd like to refer you to this older post.

Answer 2

For a VBA solution, if your translations don't change that often, you could populate a Dictionary (add a reference via Tools -> References... to Microsoft Scripting Runtime) to hold the translations.

Then you could pass in the value of the cell, split it, and return the corresponding translation for each part:

Dim dict As Dictionary

Function Translations(s As String) As String
    If dict Is Nothing Then
        Set dict = New Dictionary

        Dim arr() As Variant
        arr = Worksheets("Sheet1").Range("A1:B25").Value
        
        Dim rowCount As Integer
        rowCount = UBound(arr, 1)
    
        Dim row As Integer
        For row = 1 To rowCount
            dict(arr(row, 1)) = arr(row, 2)
        Next
    End If

    Dim temp() As String ' holds both the keys from the current input, and the results
    temp = Split(s, ",")
    
    Dim ubnd As Integer
    ubnd = UBound(temp)
    
    Dim i As Integer
    For i = 0 To ubnd
        Dim key As String
        key = temp(i)
        If dict.Exists(key) Then
            temp(i) = dict(key)
        Else
            temp(i) = ""
        End If
    Next
    
    Translations = Join(temp, ",")
End Function

and you could call it from a cell like this:

=Translations(A1)

Note that for efficiency, the dictionary is not recreated on each function call, but stored in memory. Therefore, if your translations change, you'll need to reset the dictionary. This can be done in a number of ways:

• Close and reopen the workbook

• In the VBA editor, click Run -> Reset from the menus

• Write a special method that clears the dictionary:

  Sub ClearDict
      Set dict = Nothing
  End Sub
  

  and call it (e.g. from the Immediate pane) after changes have been made.