Matching An Approximate Number

    I have a cell lets say A1 in which it is calculated by goal seek a percentage number, and on the other hand I have a table that has a range of percentages for example 10% 20% 30%... 100% for each percentage there is a certain number designated to it i.e. 10%---2 20%----4 etc..


    What I am trying to achieve is that by telling the computer look up the percentage value of A1 and look at the percentage rage in my table and put the number that matches with the return. I normally use an =Index(..match(..)) formula but in this case my problem is that the ammount that I can get on A1 is not a rounded ammount like 30% I can obtain numbers like 31%, 32% Is there a way I can set up in my frmula so that it may find the closest number in the table range.


    So if on cell A1 I have 35% to look up 30% on the table, but if it is over 35% to use 40% and so on.


    Thank you!!!

    Re: Matching An Approxiate Number


    In your Index(...Match(... formula can you instead of using A1 use Round(A1-.000001,2)? The reason I use -.000001 is so that 35% will round down to 30%, otherwise it will round up to 40%.

    Re: Matching An Approximate Number


    Or extend your table to give all the values you may want to pick, and omit the 0 from the end of the MATCH formula (see the Help pages to see the effect of doing that).

    Re: Matching An Approximate Number


    Lil,


    A plain VLOOKUP formula will return the number next to the percentage that is the next lowest in the lookup table.


    Assuming the percentage you want to look up is in K1, the lookup table is in $K$7:$L$17, and the number you want returned is in column L of the lookup table and the following table values:


    0 % 5
    10 % 6
    20 % 7
    30 % 8
    40 % 9
    50 % 10
    60 % 11
    70 % 12
    80 % 13
    90 % 14
    100 % 15

    =VLOOKUP(K1,$K$7:$L$17,2)


    will return 10% for all values in K1 < 20% >= 10%.


    Jim

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login