excel formula needed to lookup values

    Hello


    I need an excel formula that can do the following ( in the attached excel sheet)


    1 . Look up the value from input sheet from rows 8 and 9


    2 . Convert the value in row 6 which is in month to completed years. If this is less than 1 then value will be 1. Place this value in row 7 of input sheet


    Match the value from rows 7, 8 and 9 to the corresponding value in output sheet to give a final rate for a particular year.

    I don't get your number 2 request. What would be the expected result in B7 given the current parameters in your sheet?


    Also, what is the expected final result?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Hello


    for number 2 .


    we have input as Dur_M as 6 wich means 6 months and when we convert it to year we should get 1 as the expected output. I am giving an example below to let you know what exactly I want.


    Case 1 : DUr_M = 6 , output should be 1


    Case2 Dur_M = 14 , output should be 2


    Basically Dur_m reperesents the number of months a particular thing is in force and we need to convert it into years

    Then perhaps, in B7 try: =ROUNDUP(B6/12,0)


    then final formula, try: =SUMIFS(INDEX(output!$D$2:$R$6,,MATCH("YR_"&B7,output!$D$1:$R$1,0)),output!$A$2:$A$6,B9,output!$B$2:$B$6,B8)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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

Register Yourself Login