How to reference or vlookup a list of values based on a comma separated list of column references within a cell in excel?
- by glallen
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?
