Excel Problem

vivienne3608

New Member
Joined
Sep 10, 2014
Messages
17
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>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

vivienne3608

New Member
Joined
Sep 10, 2014
Messages
17
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​
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top