I want to do a vlookup (or similar) against a column which is a list of values. This works fine for looking up a value from a single row, but I want to be able to look up multiple rows, sum the results, and divide by the number of rows referenced.

For example:

   A     B            C       D        E             F               G
   [----given values----------------]  [Work/Auth]   [sum(vlookup(each(G),table,5))
                                                      /count(G)]     [given vals]

1  Item  Authorized   OnHand  Working  Operational%  DependencyOR%   Dependencies 
2  A     1            1       1        1              .55            B 
3  B     10           5       5         .50           .55            C,D
4  C     100          75      50        .50           .60            D
5  D     10           10      6         .60          1              

I want to be able to show an Operational Rate, and an operational rate of the systems each system depends on (F). In order to get a value for F, I want to sum over each value in column-E that was referenced by a dependency in column-G then divide by the number of dependencies in G. Column-G can have varying lengths, and will be a comma separated list of values from column-A.

Is there any way to do this in excel?