Sum multiple values returned vertically from vlookup-CodePudding

I have the following table which I need to have all the values found from multiple ranges vertically. I was using the following formula, but I'm only getting 1 result and not 7. After getting all those 7 values, I need to add them together in order to get a total.

So far my guess was to try the 2nd formula but obviously is not returning what I need but maybe is something around those corners.

=SUM(ARRAYFORMULA(VLOOKUP(E4,{range0,range1,range2,range3,range4,range5,range6},5,false)))

=SUM(ARRAYFORMULA(VLOOKUP(E4,{range0,range1,range2,range3,range4,range5,range6},{5,5,5,5,5,5,5},false)))

CodePudding user response：

You can use FILTER instead:

Formula:

=SUM(INDEX(FILTER(B6:F, B6:B = "name1"),,5))

Use FILTER to get the rows of B6:F where "name1" is found in B6:B Use INDEX to get only the specific column which is in our case, 5 Then SUM the column.