Results 1 to 7 of 7

Thread: Excel 2003 Look-up Formula Problem

  1. #1

    Excel 2003 Look-up Formula Problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    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?

  3. #3
    Hi,

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

    Thank you.

  4. #4
    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

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by vivienne3608 View Post
    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



    Im glad you resolved it. I had some difficulty in finding a formula that provided a complete solution, so I wondered if you could post the solution you have ?

    Thanks
    Hercules

  6. #6
    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

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Thanks Vivienne
    I had my MAX function in the wrong place .
    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
    Last edited by Hercules1946; 2014-10-31 at 06:25 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •