Not extensively tested and no error checking, but this returns the results
you describe.

Array entered** :

=MAX(A1:INDEX(A1:A$7,MATCH(TRUE,SUBTOTAL(9,OFFSET( B1:B$7,,,ROW(B1:B$7)-MIN(ROW(B1:B$7))+1,1))C$1,0)-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
There are a list of numbers under column A and B, and there is a given
number
in cell C1, such as 100

A B
2 4
4 13
7 45
5 33
9 23
13 40
23 12

For the first number in cell D1, starting from cell B1 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
first number in cell D1,
4+13+45+33 = 95, but adding the next number 23, then it will be
4+13+45+33+23 = 118. After that, I would like to determine the max. values
under column A: 2,4,7,5, but not including 9, and it should return 7 in
cell
D1.

For the second number in cell D2, starting from cell B2 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
second number in cell D2,
13+45+33 = 91, but adding the next number 23, then it will be
13+45+33+23 = 114. After that, I would like to determine the max. values
under column A: 4,7,5, but not including 9, and it should return 7 in cell
D2.

For the third number in cell D3, starting from cell B3 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D3,
45+33 = 78, but adding the next number 23, then it will be
45+33+23 = 101. After that, I would like to determine the max. values
under
column A: 7,5, but not including 9, and it should return 7 in cell D3

For the fourth number in cell D4, starting from cell B4 counting downward
in
order to determine the sum of number, which is less than / equal to 100,
but
if adding the next number, then it will more than 100. In this case for
the
third number in cell D4,
33+23+40 = 96, but adding the next number 12, then it will be
33+23+40+12 = 108. After that, I would like to determine the max. values
under column A: 5,9,13, but not including 23, and it should return 13 in
cell
D4

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric