Excel 2003 Look-up Formula Problem

Good afternoon,

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:

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

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. (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).


Any help is greatly appreciated.

Thank you

Vivienne

Hello
You don't actually show the proposed value that would be the correct choice for your example, presumably because your formula is returning zero. Is the following correct:
For the Matching Group and stage;
You are wanting a value from Col A
That is the closest to the current value, either Above or Below Current Value, but with preference to Above ?

In your example theres no higher value so 33334 would be chosen?

Hi,

Yes that is correct. both your understanding and the fact that 33,334 would/should be the answer.

Thank you.

Hi Hercules1946

The formula is:

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)),MAX(IF($A$2:$A$15=H2,INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))))
Ctrl+Shift+Enter

Regards

Vivienne

Thanks Vivienne
I had my MAX function in the wrong place :embarassed:.
I saw somehere that you were asking about the double unary --
Its a way of converting a string representation of numbers into the number equivalent, so that you can do maths on it.
Lets say you have 8 numbers divided into group "red" and group "Blue". In order to get the quantities for the "Red" group,
your formula might say (A1:A8 = "Red"), returning {TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE FALSE}. assuming the red group is in the first 4 rows
Now if our quantities for calculatng are in row B (ie red = B1:B4) we can eliminate B5:B8 easily if we can multiply the trues by 1 and the falses by 0
IF we add the double unary to our formula --(A1:A8= "red") This returns {1,1,1,1,0,0,0,0}. When this array is multiplied by the quantities in B1:B8,
B5:B8 is eliminated at the next stage as those quantities get multiplied by 0.
Its worth noting that you can get the same result by simply adding zero,provided that your doing it to a string representation of numbers, (which includes
TRUE and FALSE).

HTH
Hercules