Matching a Value with a list
 

I understand the matching value formula in excel, but I'm looking for
something a little more complicated. I'm trying to take a value in column B,
and see if any combination of items in A equal that value, and if it does
what items are used to equal the amount in column B. I'm looking for
whatever help I can get. An example is below, and thanks for you help.

Column A Column B
1 4
2 7
5
47
544

For 4, the there would be no possiblity, as none of the numbers added
together can equal 4. The answer for 7 would be 5+2.

Matching a Value with a list
 

Well, for just a simple two-component sum, one way would be to make your list
into a matrix, with the intersections being the sum of the two legs. This
use thei Function from Gary" Student. Put the code in a regular module and
use this formula, where A1:F6 is the range of your matrix and H1 is the value
you wish to look up.

=Titles(A1:F6,H1)

Function titles(r As Range, vv As Range) As String
Dim rr As Range, s1, s2 As String, gotit As Boolean
titles = ""
gotit = False
v = vv.Value ' NEW LINE

For Each rr In r
If rr.Value = v Then
gotit = True
Exit For
End If
Next
If gotit = False Then Exit Function

s1 = Cells(r.Row, rr.Column)
s2 = Cells(rr.Row, r.Column)
titles = s1 & Chr(10) & s2
End Function

hth
Vaya con Dios,
Chuck, CABGx3



"Vick" wrote:

I understand the matching value formula in excel, but I'm looking for
something a little more complicated. I'm trying to take a value in column B,
and see if any combination of items in A equal that value, and if it does
what items are used to equal the amount in column B. I'm looking for
whatever help I can get. An example is below, and thanks for you help.

Column A Column B
1 4
2 7
5
47
544

For 4, the there would be no possiblity, as none of the numbers added
together can equal 4. The answer for 7 would be 5+2.