VLookup - Wksht 1 has multiple values in 1 cell - search for values in columns to the right of matched values in Wksht 2, return the sum in wksht 1

Question

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?

Answer 1

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.