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?


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


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.

  • Col-G will have varying numbers of comma-separated values. I will have to read up on how you are doing this with array formulas, and wildcards? But I'm working in a table, so my actual formula looks more like =AVERAGE(IF(COUNTIF(Table1[[#This Row],[Dependencies]],"*"&[Asset]&"*")*([Asset]<>""),[EquipReadiness])) It shouldn't make a difference between cell-references and table-references, but ... this formula is reporting all zeros.
    – glallen
    Jun 20 '12 at 20:22
  • You'd have to use the table name with all the columns so I think that should be this: =AVERAGE(IF(COUNTIF(Table1[[#This Row],[Dependencies]],"*"&Table1[Asset]&"*")*(Table1[Asset]<>""),Table1[EquipReadiness])). That works for me. It's an array formula so you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar Jun 20 '12 at 21:15
  • CTRL+SHIFT+ENTER does work much better. So, this mostly works, although I can't seem to use an if or iferror to get rid of the #DIV/0. Also: this only seems to work if I put N, in the G column instead of N where N is the reference. Otherwise it does work.
    – glallen
    Jun 21 '12 at 16:38
  • Do you mean a literal "N" or is the reference a number? For avoiding errors you can just wrap the whole formula in IFERROR like =IFERROR(original_formula,0) the zero there is what you get in place of error, if you want blank use "" Jun 21 '12 at 17:27

as you have a variable amount of values, the easiest way is to use a UDF (User defined function)

This should get you your answer:

Function CSVAverage(CSVList As String, DataRange As Range, NumberColumn As Long) 
Dim FindList() As String
Dim NumEntries As Long
Dim I As Long
Dim Tot As Double

FindList = Split(CSVList, ",")
Tot = 0
NumEntries = UBound(FindList) - LBound(FindList) + 1

On Error Resume Next
For I = LBound(FindList) To UBound(FindList)
    Tot = Tot + Application.WorksheetFunction.VLookup(FindList(I), DataRange, NumberColumn, False)
    If Err.Number <> 0 Then
        CSVAverage = CVErr(xlErrNum)
        Exit Function
    End If
Next I
CSVAverage = Tot / NumEntries
End Function

If you have a value that is not found, then the function will return #NUM!
using your example spreadsheet, F1 would have =CSVAverage(G1,A2:E5,5)

  • I haven't used vbscript before (though I've played with C and perl some), but setting this up in my spreadsheet, adding a breakpoint to the function, and then stepping through it - everything looks good until Application.WorksheetFunction.VLookup(FindList(I), DataRange, NumberColumn, False) When I set a watch this never returns a value.
    – glallen
    Jun 20 '12 at 20:08
  • this is the VBA way of using the vlookup function. For each of the values you have separated by commas, it will look for the value in column 1 of your range, and return the matching value in column NumberColumn of that range. if you want to try the function manually, then you can use VLookup("A", Range("A1:B5"), 2, False) to find A in area A1:B5, and return the value in the 2nd column
    – SeanC
    Jun 20 '12 at 20:14
  • Also, if the value is not found in your table, it will return 0, and the err.number will be set to indicate this.
    – SeanC
    Jun 20 '12 at 20:33
  • Yes, I started off using VLookup directly in Excel and was trying to figure out the glue to make it look-up multiple times based on multiple references. Hence the question. It works when I call it directly in Excel, but not in VB. I set a watch on it, and also tried calling it manually, ie foo= Application.WorksheetFunction.VLookup("3",Range("Table1"),8,False) It may be that I'm trying to pass a table to it instead of a cell range? In my actual file, I've tried it both ways: =CSVAverage(Table1[[#This Row],[Dependencies]],Table1,9) and, =CSVAverage(L3,$A$3:$I$55,8) no luck either way.
    – glallen
    Jun 20 '12 at 20:38
  • It will return 0 if there is no value in G, but otherwise it yields either #NUM!
    – glallen
    Jun 20 '12 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.