Multiple Variable Formula

[C.W.]

I have the following:

A table with the following data:
AR LA MS OK

<12/31/1984 8.00% 8.00% 8.00% 7.00%

12/31/1984 6.00% 6.00% 8.00% 6.00%

I also have the two variables in which I would like to look up on a
calculation worksheet that uses the data from above in an interest
calculation. The varaibles are as follows and can change depending on the
state in which the loan is being calculated:

State: MS

Loan Issue Date: 01/15/1979

What I am looking for is to be able to automatically populate a cell based
on the variable input. I want the cell to autopopulate by looking at the
variables and cross referencing the variables to the table based on the state
and the issue date so that the person doing the calculation only has to fill
in information and nothing else.

Thanks,

C.W.

 

[Pete_UK]

It always helps if you can quote specific cell references that YOU
use, so that the formulae we suggest are tailored to your situation.
Without that we have to make assumptions regarding the data layout,
which you then have to translate into your worksheet.

So, assuming your table of interest rates is in a sheet called Data
and occupies A1:E3, and that a Calculation sheet is used to record the
State in B1 (better to have a validation drop-down here) and the Loan
Issue Date in B2, and that you want to return the appropriate interest
rate to cell B3, then put this in B3:

=IF(OR(B1="",B2=""),"",INDEX(Data!
B2:E3,IF(YEAR(B2)<=1984,1,2),MATCH(B1,Data!B1:E1,0))

Hope this helps.

Pete

 

[Pete_UK]

Sorry, missed a bracket from the end:

=IF(OR(B1="",B2=""),"",INDEX(Data!
B2:E3,IF(YEAR(B2)<=1984,1,2),MATCH(B1,Data!B1:E1,0)))

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi,

Your discussion leaves a problem for 12/31/1984, that date is not covered by
any of your conditions.

Suppose you enter the table in cells A1:E3

The your two variables in A6 and A7 with A6 being the state. You formula
would be:
=VLOOKUP(A7,A2:E3,MATCH(A6,B1:E1,0),TRUE)

However you should remove the entry <12/31/1986 from cell A2, just enter any
very early date or the number 0 will do. Also change the >12/31/1984 to a
date 12/31/1984. With this setup 12/31/1984 will be considered part of the
second tier of rates, if you want it to be part of the first tier, then
change the date in A3 to read 1/1/1985.