I am using the VLOOKUP function to search for values in one workbook (Workbook 2 below, highlighted in blue cell F513 - 515,600.05 and cell F518 - 96,560.46) 6 columns to the right of column A, based on multiple values in another workbook, within ONE cell (Workbook 1 below, highlighted in yellow cell A15 – 401-05-0000, 403-01-0000). I would like the results to be added together and the result placed in one cell (Workbook 1 highlighted in green cell G15. How should I modify the VLOOKUP function below at the red arrow to do this?

## 1 Answer

If you have Excel for MS365, you could try SUM with VLOOKUP and TEXTSPLIT:

```
=SUM(VLOOKUP(TRIM(TEXTSPLIT(A15,",")), '[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834, 6, FALSE))
```

With older versions of Excel, you could add multiple VLOOKUP's together, using some variation of LEFT, RIGHT or MID to pick out each individual *lookup_value*. For example:

```
=VLOOKUP(TRIM(LEFT(A15,FIND(",",A15)-1)), '[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834, 6, FALSE)
+VLOOKUP(TRIM(RIGHT(A15,LEN(A15)-FIND(",",A15))), '[BVAR Balanza julio 2023.xlsx]Balanza de Comprobación'!$A$387:$F$834, 6, FALSE)
```

This method is not dynamic, however. If the number of *lookup_values* changes, you would need to modify it accordingly.

Another option worth considering is to have a separate row for each *lookup_value*, then GROUP the desired rows together (**Shift+Alt+Right**; or, on the Ribbon, go to the **Data Tab > Outline > Group**). Add a subtotal at the bottom of each group, then use the expand/collapse (+/-) buttons in the left-hand margin to show/hide details.