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
```

`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 findingexactlywhat 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