I am trying to create a formula to look up a value in the table shown in the screenshot. The inputs are AGE, which is in column A, and then the other input is Interest Rate.
For example, if Age: 42 and Interest Rate: 4.375 then I would like the formula to return 0.235, which is the value next to 4.375 in the table.
There are separate tables for each whole percentage (e.g. 3%, 4%, ..., 18%).
One approach could be to use INDEX in Reference Mode and define each Interest Rate as an area_num, and then use Index Match Match to find the value, and then return the value to the right...?
I am not sure how to do this or if it is even the most efficient way, so I would sincerely appreciate help in how best to accomplish this.
Thank you!
image of table I would like to search
Tried Index Match Match but could only get interest rate within a single Interest Rate portion of the larger table, and I want to get the value next to the Interest Rate and be able to search the whole table.
The formula to get the result is:
=INDEX(A:Q,3+Age-18+(INT(InterestRate)-3)*83,16*MOD(InterestRate,1)+3)
Probably, this formula will be resided on a sheet different from the Interest Rates table, so A:Q should be used with sheet's name as depicted below.
Name the appropriate cells for Age and InterestRate.