• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching a range of values to a value

E

ExcelNoob

New Member
Hello guys, could any kind soul please help me with the following question.

Column A is my range of values, I want to find values in that column that is equal to a fixed number that I determine.

For example:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5

The value I want to match is 9 and the formula will be able to tell me that cells A2 + A3 + A4 or A4 + A5 will give me 9.

Thanks in advance!
 
ExcelNoob said:
Hello guys, could any kind soul please help me with the following question.

Column A is my range of values, I want to find values in that column that is equal to a fixed number that I determine.

For example:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5

The value I want to match is 9 and the formula will be able to tell me that cells A2 + A3 + A4 or A4 + A5 will give me 9.

Thanks in advance!
Click to expand...
Hi,

This isn't an easy thing to do. The best code I've seen for solving this was written by Harlan Grove
ExcelNoob said:
Hello guys, could any kind soul please help me with the following question.

Column A is my range of values, I want to find values in that column that is equal to a fixed number that I determine.

For example:
A1 = 1
A2 = 2
A3 = 3
A4 = 4
A5 = 5

The value I want to match is 9 and the formula will be able to tell me that cells A2 + A3 + A4 or A4 + A5 will give me 9.

Thanks in advance!
Click to expand...
Hi,

Don't know what happened to my previous attempt to respond!! Such is life. This isn't an easy problem to solve because there can be many answers depending upon the size of the data set. The best solution I've used was coded by Harlan Grove and there are many copies available on the Web including the one at the link below. the code is called FINDSUMS

LINK
 
@ExcelNoob

Excel has a feature called Solver which is an add-in through which you can get possible values. When I tried your case I got 2 combinations. The same can be mixed with lookup function to return the address of cells like A1, A2.

Regards,
 
You must log in or register to reply here.
Back
Top