Index match match formula or similar for multiple look-up values/an array of look-up values

Question

I have a datasheet that is basically column A has company names (A thru Z for simplicity) while columns B through F have financial information (stock amounts, retained earnings, ect).

(row 1) Company   Stock     Dividends      Net Income     Retained Earnings
(row 2) A          5.4         7.6           44.5              57.5
(row 3) B          8.2         8.4           78.6              88.9
(row 4) C          13.4        2.2           14.4              14.5
(row 5) D          4.7         5.4           8.9               16.7
...
(row 27)Z          5.6         8.4           12.5              11.1

(row 29)Sum of the following companies:
(row 30)A         Stock
(row 31)C         Dividends
(row 32)D         Net Income
(row 33)Z         Retained Earnings

I am trying to return the sum of multiple companies from a data table based on the column name. For Instance, I would want to find the total stock amount for company A,,C,D & Z (this will change frequently, so I wanted to find a non-hard coded method like typing the values into a {}). I would want the formula to reference a cell with the names of the columns as well since the actual data table has about 15 different column variables.

What I have tried to do so far is to incorporate an array into an index match match like below into the cell to the left of "stock" in row 30:

+INDEX($B$2:$F$27,MATCH($A$30:$A$33,$A$2:$A$27,0),MATCH($B30,$B$1:$F$1,0))

However, since I don't think I can use an array for a lookup value in the match formula, I come out with #N/A.

Does anyone have any suggestions as to what I can do?

Answer 1

I know that you asked about a formula, but wonder if a PivotTable and slicer(s) might not do what you want with minimal effort. Here, for example, it displays the total financial amounts, including stocks, for companies A,C,D and Z. BTW it took less time to produce the PivotTable and slicer than it has taken to type this answer.