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
If you're on Windows, here's an example to try:
FILTERXML
to split by ,
VLOOKUP
with exact matches (FALSE
as the last parameter), we need to strip spaces in values - with SUBSTITUTE(…," ","")
--
is used to convert text numbers (result of SUBSTITUTE
) to numbers=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))