Index Match using < operator

    Hi,


    I am using an Index Match formula to determine a charge from a table.


    The criteria are Currency and Number of days. I dont fully understand the function and so am having trouble in correcting it as it is producing unexpected results.


    The formula is INDEX('Charges Table'!$C$2:$R$13,MATCH(P79+1,'Charges Table'!$B$2:$B$13,1),MATCH(G79,'Charges Table'!$C$1:$R$1,0))


    I have a number of days of 48 and this should return a charge of 3.5, being in the range more than 8 and less than 183 but it is returning 3.06


    The attached table explains it better than i ever could in words!


    What i need is a formula that can determine if the number of days is less than a number but greater than another.


    7 DAYS 8< 3.06


    8 DAYS TO
    6 Months 183< 3.5


    Perhaps someone could help me out by taking a look and give me an idea of what i need to do to correct the formula.


    Thanks in advance for your time.


    Neil

    Re: Index Match using &lt; operator


    Maybe


    =INDEX('Charges Table'!$C$2:$R$13,SUM((MATCH(I4,'Charges Table'!$B$2:$B$13,1)),1),MATCH(C4,'Charges Table'!$C$1:$R$1,0))


    Tell me if that's messed up.


    Bubbis

    :silverha:

    Re: Index Match using &lt; operator


    Actually, that was just a correction to your formula. I'll work on getting it to where you want --unless someone else beats me to it.

    :silverha:

    Re: Index Match using &lt; operator


    Maybe this works for you?


    Code
    =INDEX('Charges Table'!$C$2:$R$13,IF(I4<2,0,MATCH(I4,'Charges Table'!$B$2:$B$13,1))+1,MATCH(C4,'Charges Table'!$C$1:$R$1,0))


    put in cell J4

    Re: Index Match using &lt; operator


    Heck, I'd just subtract 1 from every value in column B, get rid of the 'less than' signs, and then use the formula


    =INDEX('Charges Table'!$C$2:$R$13,SUM((MATCH(I4,'Charges Table'!$B$2:$B$13,1)),1),MATCH(C4,'Charges Table'!$C$1:$R$1,0))


    in column J. Hope that helps.


    Bubbis Thedog

    :silverha:

    Re: Index Match using &lt; operator


    Thanks very much Bubbis that works. I will have to wait till im back at work in the morning before testing it on the full data.


    I think i need to learn the logic behind it so i can fix myself in future.


    Thanks to both Bubbis and Seti for the responses.



    Cheers
    Neil

Participate now!

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

Register Yourself Login