Vlookup multiple values separated by semicolon

Question

I need help to align same values from two columns where there are more than one code separated by semicolon in one single cell.

I have one column like that:

UMLS CODE
C0443147
C0441748;C4020899
C4025900
C0085606;C3544092;C4020898

And i need to match the following data with the column above.

UMLS CODE  TYPE    MEDDRA CODE         DEFINITION
C0443147    LT;PT   10014275;10014407   EEG;Electroencephalogram
C4020899    LT;PT   10014544;10014430   EMG;Electromyogram
C3544092    OL;LT   10014828;10014449   Electronystagmography
C0013854    PT;LT   10014455;10014359   Electro-oculogram

So the result matching the UMLS CODES column must be like this:

UMLS CODE                  UMLS CODE  TYPE    MEDDRA CODE         DEFINITION
C0443147                   C0443147   LT;PT   10014275;10014407   EEG;Electroencephalogram
C0441748;C4020899          C4020899   LT;PT   10014544;10014430   EMG;Electromyogram
C4025900                   -------    -----   -----------------   -------------------
C0085606;C3544092;C4020898 C3544092   OL;LT   10014828;10014449   Electronystagmography

I tried the following formula on excel but didnt work when the looking value has more than one value separated by semicolon.

=VLOOKUP($A1;$A$13819:$D$63379;COLUMN(A:A);0)

Where $A1 is the UMLS CODE and $A$13819:$D$63379 is all the data to match with UMLS CODE.

Pictures from UMLS codes:

Picture from UMLS data:

Result desired but working also in multiple value cells separated by semicolon from UMLS code:

Answer 1

assuming you have a maximum number of UMLS codes in your list (I have assumed 3)

The table on row 8-12 is an intermediary step just to simplify this can be put in the final function if you so choose.

=TRIM(MID(SUBSTITUTE($A5;";";REPT(" ";LEN($A5))); (C$8)*LEN($A5)+1;LEN($A5)))

=IFERROR(VLOOKUP($A12;$C$2:$F$5;4;FALSE);IFERROR(VLOOKUP($B12;$C$2:$F$5;4;FALSE);VLOOKUP($C12;$C$2:$F$5;4;FALSE)))

Column A16 is just = A2 etc

Answer 2

@S.Ram,

Import the data in Sheet1 as in the picture:

Import the data in Sheet2 as in the picture:

and try:

Option Explicit
Option Explicit

Sub test()

Dim LastRow1 As Long
Dim LastRow2 As Long
Dim i As Long
Dim j As Long
Dim Word As String
Dim Word1 As String
Dim Word2 As String
Dim SpecialChr As Long
Dim Position As Long
Dim Position2 As Long

LastRow1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheet2.Range("B" & Rows.Count).End(xlUp).Row

 With Sheet1

    For i = 2 To LastRow1

        SpecialChr = (Len(.Range("A" & i).Value) - Len(Replace(.Range("A" & i).Value, ";", ""))) / Len(";")

        If SpecialChr = 0 Then
            Word = .Range("A" & i).Value
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    End If
                Next j
            End With
        ElseIf SpecialChr = 1 Then
            Position = InStr(1, .Range("A" & i).Value, ";")
            Word = Left(.Range("A" & i).Value, Position - 1)
            Word1 = Right(.Range("A" & i).Value, Position - 1)
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    ElseIf .Range("B" & j).Value = Word1 Then
                        .Range("B" & j).Offset(0, -1).Value = Word1
                    End If
                Next j
            End With
        ElseIf SpecialChr = 2 Then
            Position = InStr(1, .Range("A" & i).Value, ";")
            Position2 = InStr(Position + 1, .Range("A" & i).Value, ";")
            Word = Left(.Range("A" & i).Value, Position - 1)
            Word1 = Mid(.Range("A" & i).Value, Position + 1, Len(.Range("A" & i).Value) - Position2)
            Word2 = Right(.Range("A" & i).Value, Position - 1)
            With Sheet2
                For j = 2 To LastRow2
                    If .Range("B" & j).Value = Word Then
                        .Range("B" & j).Offset(0, -1).Value = Word
                    ElseIf .Range("B" & j).Value = Word1 Then
                        .Range("B" & j).Offset(0, -1).Value = Word1
                    ElseIf .Range("B" & j).Value = Word2 Then
                        .Range("B" & j).Offset(0, -1).Value = Word2
                    End If
                Next j
            End With
        End If
    Next i
End With

End Sub

Finnaly, the results will be import in Sheet2 yellow area.