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

## Files

• 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!