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

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

`=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.

• 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. 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. 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)

``````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. 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 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. 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. Jun 20 '12 at 20:38
• It will return 0 if there is no value in G, but otherwise it yields either #NUM! Jun 20 '12 at 20:42