How to let Excel pick up several numbers that give a sum wanted?

I'm not sure how I can phrase the question better but please see the image attached below. Basically, in the example, I have a number as the goal, 10 (on the left). Then, I have a number list on the right with a bunch of numbers that I can choose from. I'm looking for a way to select a certain number of numbers from the number list to make the sum of those numbers equal to the goal number. In the example below, the correct answer is a choice of "5", "3", and "2".

Any excel functions, tools in the menu bar, or VBA codes are welcome.

I have managed to make a VBA solution for this. I have tested with multiple different goals/targets & different range of numbers to sum and worked every time. Can't guarantee there isn't a problem it won't work around though.

Here it is:

Note - you should be able to now have multiple of the same number. This will only return the first solution it finds. It doesn't find EVERY solution.

Sub SumSolver()

Dim rng, Goal As Double, ws As Worksheet, i As Long, j As Long, Answer As Double, k As Long
Dim lRow As Long, Answerlist As String, LastAdded As Long, AnswerListPos As String
Dim c As Range, RngToSplit As String, AnswerArray, AnswerItem

Set ws = Sheets("Sheet1") 'Change Sheet1 to your sheet name
lRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row 'Change to needed column
ws.Range("C2:C" & lRow).ClearContents 'Clear output range if needed

For Each c In ws.Range("B2:B" & lRow) 'This loop populates the list range into a string
    If c.Value <> "" Or Not IsNumeric(c.Value) Then 'Checking for empty or non-numeric values
        If RngToSplit = "" Then
            RngToSplit = c.Value
        Else
            RngToSplit = RngToSplit & "," & c.Value
        End If
    End If
Next
rng = Split(RngToSplit, ",") 'Split the new list string into an array

If Not IsNumeric(ws.Range("A2").Value) Then 'Checks target value is actually a number
    MsgBox "The target value is not a valid number. Please correct this before trying again.", vbExclamation, "Sum Solver"
    Exit Sub
Else
    Goal = ws.Range("A2").Value 'Value of the goal/target
End If

For i = 0 To UBound(rng) ' 0 = start of array, Ubound = End of array
    If rng(i) = Goal Then
        ws.Range("C2") = rng(i)
        Answerlist = rng(i)
        GoTo SubExit
    ElseIf rng(i) < Goal Then
        Answer = rng(i)
        Answerlist = rng(i)
        AnswerListPos = i
        For j = i + 1 To UBound(rng)
            If Answer + rng(j) = Goal Then
                Answerlist = Answerlist & "," & rng(j)
                AnswerListPos = AnswerListPos & "," & j
                GoTo SubExit
            ElseIf Answer + rng(j) < Goal Then
                Answer = Answer + rng(j)
                LastAdded = j
                If Answerlist = "" Then
                    Answerlist = rng(j)
                    AnswerListPos = j
                Else
                    Answerlist = Answerlist & "," & rng(j)
                    AnswerListPos = AnswerListPos & "," & j
                End If
            End If
            If j = UBound(rng) Then
                If LastAdded = UBound(rng) Then
                    Answerlist = Left(Answerlist, InStrRev(Answerlist, ",") - 1)
                    AnswerListPos = Left(AnswerListPos, InStrRev(AnswerListPos, ",") - 1)
                    Answer = Answer - rng(j)
                    LastAdded = Val(Mid(AnswerListPos, InStrRev(AnswerListPos, ",") + 1))
                End If
                If LastAdded > 0 Then Answer = Answer - rng(LastAdded)
                If InStr(Answerlist, ",") = 0 Then Exit For
                j = Val(Mid(AnswerListPos, InStrRev(AnswerListPos, ",") + 1))
                Answerlist = Left(Answerlist, InStrRev(Answerlist, ",") - 1)
                AnswerListPos = Left(AnswerListPos, InStrRev(AnswerListPos, ",") - 1)
            End If
        Next j
    End If
    Answerlist = ""
Next i

SubExit:

If Answerlist <> "" Then
    i = 2
    AnswerArray = Split(Answerlist, ",") 'Split the result into an array
    For Each AnswerItem In AnswerArray
        ws.Range("C" & i) = AnswerItem 'Output the results into the sheet
        i = i + 1
    Next
Else
    MsgBox "No possible combination found for a target value of " & Goal & ".", vbExclamation, "Sum Solver"
End If

End Sub

EDIT: Just updated to account for if there are any blank rows in the list range as well as handle if a value is non-numeric. Actually made it half a second faster (13s) for a 12 item list of 1000 iterations.

You will see the rows I've made comments on are the ones you need to change. Pretty much just what columns it is working on and the starting cell It works looking at the last row but if you don't need it then just replace for example "B2:B" & lRow with B2:B5 etc.

I've also incorporated it into a function. Used as:

=SumSolver(Target value, Range of sum values)

It returns the results in the same cell separated by a comma. This can be changed to another method easily if needed though.

Function SumSolver(Goal As Double, ListRange As Range)

Dim i As Long, j As Long, Answer As Double, k As Long, rng As Variant
Dim Answerlist As String, LastAdded As Long, AnswerListPos As String

rng = Application.Transpose(ListRange)

For i = 1 To UBound(rng)
    If rng(i) = Goal Then
        Answerlist = rng(i)
        GoTo SubExit
    ElseIf rng(i) < Goal Then
        Answer = rng(i)
        Answerlist = rng(i)
        AnswerListPos = i
        For j = i + 1 To UBound(rng)
            If Answer + rng(j) = Goal Then
                Answerlist = Answerlist & "," & rng(j)
                AnswerListPos = AnswerListPos & "," & j
                GoTo SubExit
            ElseIf Answer + rng(j) < Goal Then
                Answer = Answer + rng(j)
                LastAdded = j
                If Answerlist = "" Then
                    Answerlist = rng(j)
                    AnswerListPos = j
                Else
                    Answerlist = Answerlist & "," & rng(j)
                    AnswerListPos = AnswerListPos & "," & j
                End If
            End If
            If j = UBound(rng) Then
                If LastAdded = UBound(rng) Then
                    Answerlist = Left(Answerlist, InStrRev(Answerlist, ",") - 1)
                    AnswerListPos = Left(AnswerListPos, InStrRev(AnswerListPos, ",") - 1)
                    Answer = Answer - rng(j)
                    LastAdded = Val(Mid(AnswerListPos, InStrRev(AnswerListPos, ",") + 1))
                End If
                If LastAdded > 0 Then Answer = Answer - rng(LastAdded)
                If InStr(Answerlist, ",") = 0 Then Exit For
                j = Val(Mid(AnswerListPos, InStrRev(AnswerListPos, ",") + 1))
                Answerlist = Left(Answerlist, InStrRev(Answerlist, ",") - 1)
                AnswerListPos = Left(AnswerListPos, InStrRev(AnswerListPos, ",") - 1)
            End If
        Next j
    End If
    Answerlist = ""
Next i

SubExit:

If Answerlist <> "" Then
    SumSolver = Answerlist
Else
    SumSolver = "N/A"
End If

End Function

Example:

I decided to do an update of my speed test. This time comparing the updated code against my original, and was surprised to see the difference. I did runs of 1000 iterations where the solver could not find a combination. I did this with screen updating on. For a list of 8 there is a max of 255 combinations, for a list of 12, there is a max of 4095 combinations (doubles with each item added). For a list of 12, that comes to 4,095,000 calculations. Updated code did that in an average of 13.6 seconds. Obviously this is on my machine, which will have different results to yours (but the ratios should still be about the same).