Creating a Function

[Guest]

I am trying to write a lookup function that will select a rate from an array
(named NIRATES), based on three crireria. What is the neatest way of writing
the function, other than using nested 'IF' statements within OFFSET/VLOOKUP?

My function needs to look something like;
RATE1 ( “IN†or “OUTâ€, “EE†or “ERâ€, lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%

 

[Guest]

Year 1992 1993 1994 1995 1996 <=== row 1
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%


IN <==== A8
EE <===== A9
1996 < =====A10
10.00% <=====A11

A11 formula is:

=INDEX($A$1:$F$5,MATCH(A8&":"&A9,$A$1:$A$5,0),MATCH(A10,$A$1:$F$1,0))
HTH

 

[Pete_UK]

Use MATCH to find the year (and thus the column), MATCH to find which
row of the 4, and both MATCH functions are contained within an INDEX
function which covers your rates.

Hope this helps.

Pete