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