Look up value in a table based on multiple variables

[00pumpkin]

Hello All,

I'm trying to create a "calculator" that will lookup a value within a grid based on three variables, one of the variables having its own set of variables.

In the attached workbook you will see drop down options in A26:C26. Based on these variables I want to produce the corresponding value from the table above in the MSR cell E26. The Programs in C3:CH are easy but for the programs G30 and G15 there are unique values based on the three digits that the rate ends with. I try to explain the differences starting in A29.

Also, I have included examples in N26 to show how the MSR should come out after the formula is working correctly.

Thanks for your help!

MSR Calculator.xlsx

[gak67]

Please Login or Register  to view this content.

[Jacc]

Here is another take on it:

Please Login or Register  to view this content.

[00pumpkin]

Here is another take on it:

Please Login or Register  to view this content.

When I try my third example using this it doesn't pull 3.12. I'm getting 1.093?

[00pumpkin]

Please Login or Register  to view this content.

This seems to work but I didn't plan on having all the writing down there starting in row 29. How can I make this cleaner?

[gak67]

I see you have data in row 1, but formatted to white text. I also did a trace dependents and trace precedents on it and came up with nothing. Does it need to be there? I was going to use row 1 so you could get rid of the writing below the table.

[gak67]

I think you are using a List Tab for the dropdown boxes. On there create a 2 column range with 0.25, 0.375, 0, 0.125 in the first column and 19, 31.5, 44, 56.5 and name the range something appropriate. Replace the "$D$29:$D$32,6" part of the VLOOKUP part of my formula with "[something appropriate],2"

[Jacc]

Ok, trying againg.

[00pumpkin]

Ok, trying againg.

This seems to work perfectly! Thanks!

[Jacc]

-----------