CODEWITHSUNDEEP
excelvbaexcel-formulalookup
ason-1412
ason-1412

Reputation: 3

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

if anyone can please assist with this

Let's call this sheet 1

enter image description here

enter image description here

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.

Upvotes: 0

Views: 3067

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15327

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.

Upvotes: 0

JvdV
JvdV

Reputation: 75850

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

enter image description here

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.

Upvotes: 3

Related Questions