Option Explicit

Public Function DPPR_Vlookup(Lookup_Value As String, Lookup_Range As Range, Coloumn_Index As Long, Optional Match_Case As Integer) As Variant

Dim D_Code() As String

Dim Pos_1 As Long

Dim i As Integer

Dim Sum_Value As Long

Dim x1 As Long
Dim x2 As Long
Dim x3 As Long


Pos_1 = InStr(1, Lookup_Value, "&", vbTextCompare)

    With Application.WorksheetFunction

        If Pos_1 = 0 Then

        DPPR_Vlookup = .VLookup(Lookup_Value, Lookup_Range, Coloumn_Index, Match_Case)

        Else

        D_Code() = Split(Lookup_Value, "&")

            Sum_Value = 0

            For i = 1 To UBound(D_Code())

                If IsError(.VLookup("DD" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)) = True Then

                x1 = 0

                Else

                x1 = .VLookup("DD" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)

                End If


                If IsError(.VLookup("DD0" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)) = True Then

                x2 = 0

                Else

                x2 = .VLookup("DD0" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)

                End If


                If IsError(.VLookup("DD00" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)) = True Then

                x3 = 0

                Else

                x3 = .VLookup("DD00" & D_Code(i), Lookup_Range, Coloumn_Index, Match_Case)

                End If


                Sum_Value = Sum_Value + x1 + x2 + x3

                x1 = 0
                x2 = 0
                x3 = 0

            Next i


DPPR_Vlookup = Sum_Value


        End If

    End With

End Function