excelexcel-formulaexcel-2010vlookup

Vlookup for items in a comma separated list


I have a table with different values associated with different name.

Column A Column B
height 1 2
width 1 5
height 2 8
width 2 9

In another sheet, I have a table with multiple values separated by comma in each cell.

Column A1 Column B1
height1, width2 Sum???
width2, height 2 sum???

I would like to use Vlookup for each name separated by comma in Column A1 and get autosum for them in Column B1 using their values in Column B.

Please help.

I have multiple formulas but it doesn’t work


Solution

  • If you're on Windows, here's an example to try:

    =SUM(--VLOOKUP(
    FILTERXML("<x><h_w>" & SUBSTITUTE(SUBSTITUTE(A8," ", ""), ",", "</h_w><h_w>") & "</h_w></x>", "//h_w"),
    SUBSTITUTE($A$2:$B$5, " ",""), 2,FALSE))
    

    Result