How do I lookup multiple values from a comma separated cell and average the results in excel?

Question

I'm trying to build a formula that can lookup multiple ISO country codes separated by comma contained in one cell (Cell A2, Image 1) with a reference to a list of country codes and education scoring (Columns F and G, Image 1). Then return the average of the scores of all countries on cell B2. does anyone know if I can build a formula to handle that?

With normal formulas,no. Your problem is that you need to split the text in each cell separated with commas, and also, you need to iterate each fragment, not a specific one, so you'll need VBA to do this, as far as I know. — Foxfire And Burns And Burns, Apr 02 '19 at 12:12
Maybe `=SUMPRODUCT(--ISNUMBER(SEARCH(F$2:F$34,A2)),G$2:G$34)/SUMPRODUCT(--ISNUMBER(SEARCH(F$2:F$34,A2)))` — BigBen, Apr 02 '19 at 12:59

score 0 · Answer 1 · answered Apr 02 '19 at 13:02

I didn't think you could do this with cell formula, but then I saw this post and came up with this:

=AVERAGE(IF(ISNA(MATCH($F$2:$F$99, TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))=1),99)), 0)), "",  $G$2:$G$99 ))

Try pasting into cell B2 as an array formula (Ctrl + Shift + Enter) and fill-down... And don't ask me how it works.

score 0 · Answer 2 · answered Apr 02 '19 at 13:09

You could try VBA:

Option Explicit

Sub test()

    Dim i As Long
    Dim strCode As String, strScore As String
    Dim rngVlookup As Range
    Dim Code As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        Set rngVlookup = .Range("F2:G34")

        For i = 2 To 3

        strCode = ""
        strScore = ""

            strCode = .Range("A" & i).Value

            For Each Code In Split(strCode, ",")
                If strScore = "" Then
                    On Error Resume Next
                    strScore = Application.WorksheetFunction.VLookup(Trim(Code), rngVlookup, 2, False)
                Else
                    On Error Resume Next
                    strScore = strScore & ", " & Application.WorksheetFunction.VLookup(Trim(Code), rngVlookup, 2, False)
                End If

            Next Code

            With .Range("B" & i)
                .Value = strScore
                .NumberFormat = "0.000000"
            End With

        Next i

    End With

End Sub

How do I lookup multiple values from a comma separated cell and average the results in excel?

2 Answers2