Excel Problem

[vivienne3608]

Good morning,

I am hoping someone can help me. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:</SPAN></SPAN>

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,</SPAN></SPAN>
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))</SPAN></SPAN>

The table below shows the higher current value and the proposed amount I need the formula to return. I know the proposed value I need appears twice, however, it doesn’t matter which cell the formula picks. The cells B2:E15 contain the proposed amounts. (</SPAN></SPAN>The proposed amount should be equal or higher than the current amount. However there are a few instances when the current amount is higher than the largest proposed option. In that case I want excel to pick that largest proposed option even if it is not equal or greater than the current amount).</SPAN>

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>		1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>
2</SPAN>	Top</SPAN>	37,930</SPAN>	39,830</SPAN>	41,830</SPAN>	44,130</SPAN>		Group</SPAN>	Bottom</SPAN>
3</SPAN>	Top</SPAN>	37,930</SPAN>	39,830</SPAN>	41,830</SPAN>	44,130</SPAN>		Stage</SPAN>	1</SPAN>
4</SPAN>	Top</SPAN>	37,245</SPAN>	39,145</SPAN>	41,045</SPAN>	43,345</SPAN>		Current Amount</SPAN>	34,719</SPAN>
5</SPAN>	Top</SPAN>	36,459</SPAN>	38,359</SPAN>	40,259</SPAN>	42,559</SPAN>		Proposed Amount</SPAN>	33,334</SPAN>
6</SPAN>	Top</SPAN>	35,774</SPAN>	37,574</SPAN>	39,374</SPAN>	41,674</SPAN>
7</SPAN>	Top</SPAN>	34,988</SPAN>	36,688</SPAN>	38,488</SPAN>	40,688</SPAN>
8</SPAN>	Top</SPAN>	34,988</SPAN>	36,688</SPAN>	38,488</SPAN>	40,688</SPAN>
9</SPAN>	Bottom</SPAN>	33,334</SPAN>	34,834</SPAN>	36,534</SPAN>	38,634</SPAN>
10</SPAN>	Bottom</SPAN>	33,334</SPAN>	34,834</SPAN>	36,534</SPAN>	38,634</SPAN>
11</SPAN>	Bottom</SPAN>	32,758</SPAN>	34,158</SPAN>	35,758</SPAN>	37,858</SPAN>
12</SPAN>	Bottom</SPAN>	32,182</SPAN>	33,582</SPAN>	35,082</SPAN>	37,182</SPAN>
13</SPAN>	Bottom</SPAN>	31,507</SPAN>	32,807</SPAN>	34,007</SPAN>	35,907</SPAN>
14</SPAN>	Bottom</SPAN>	30,931</SPAN>	32,031</SPAN>	33,131</SPAN>	34,731</SPAN>
15</SPAN>	Bottom</SPAN>	30,931</SPAN>	32,031</SPAN>	33,131</SPAN>	34,731

<TBODY>
</TBODY>

 

[vivienne3608]

Hello,

I no longer need any further replies to this issue. Thank you to all who have taken the time to try and find a solution for me I appreciate it.

Vivienne​