# Value retrieval from the multiple selection dropdown in Excel

I am trying to make calculation from my dropdown menu. I have following dropdown in sheet 1 in my Excel.

``````## Category ##
### AAA ###
### BBB ###
### CCC ###
### DDD ###
``````

In sheet 2, I have corresponding values for this dropdown.

``````## Category  Category Value##
### AAA    1###
### BBB    2###
### CCC    3###
### DDD    4###
``````

I added VBA code for multiple selection and also added simple `VLOOKUP` formula to retrieve the value of category.

``````=VLOOKUP(E2;Sheet2!I2:J5;2;)
``````

With the VBA code, I am able to select all three category and also remove the selected category later. But I am failing to retrieve the sum of selected category. E.g., if a customer chooses category AAA & CCC, he/she should be able to see sum as 4. Also, if a customer first chooses all three categories and then removes one of them, then the sum should get updated. I am not getting how do I update my `VLOOKUP` formula to get the sum.

Here is my VBA code for multiple selection.

``````Private Sub Worksheet_Change(ByVal Target As Range)
'Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
'                If xValue1 = xValue2 Or _
'                   InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
If InStr(1, xValue1, xValue2 & ",") > 0 Then
xValue1 = Replace(xValue1, xValue2 & ", ", "") ' If it's in the middle with comma
Target.Value = xValue1
GoTo jumpOut
End If
If InStr(1, xValue1, ", " & xValue2) > 0 Then
xValue1 = Replace(xValue1, ", " & xValue2, "") ' If it's at the end with a comma in front of it
Target.Value = xValue1
GoTo jumpOut
End If
If xValue1 = xValue2 Then        ' If it is the only item in string
xValue1 = ""
Target.Value = xValue1
GoTo jumpOut
End If
Target.Value = xValue1 & ", " & xValue2
End If
jumpOut:
End If
End If
Application.EnableEvents = True
End Sub
``````
• First, your `VLOOKUP` is messed up. You should use `VLOOKUP(E2;Sheet2!\$I\$2:\$J\$5;2;0)`. `\$I\$2:\$J\$5` instead of your `I2:J5`, so that when you copy the formula down, your lookup range does not go down too. `0` at the end is necessary for finding exactly what is in `E2`, `E3`, etc. If you omit the `0`, your results are no longer reliable, because then `VLOOKUP` may do approximations which you will not anticipate. – ZygD Mar 28 '19 at 12:35

``````=SUM(IF(ISERR(FIND(Sheet2!\$I\$2:\$I\$5;A1;1));0;Sheet2!\$J\$2:\$J\$5))
Also, change the `A1` to your actual dropdown cell. 