Excel – How to reference or vlookup a list of values based on a comma separated list of column references within a cell in excel

microsoft excelvlookup

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?

Best Answer

Are you looking for a formula for each row? So where you have C, D in G3 (?) I assume you want to lookup C and D in column A and average the corresponding values in column E? If so try this array formula for row 2 copied down

=AVERAGE(IF(COUNTIF(G2,"*"&A$2:A$10&"*")*(A$2:A$10<>""),E$2:E$10))

To enter the array formula you must use CTRL-SHIFT-ENTER. Excel will place {} braces around the function to indicate it is an array function.

Related Question