In a standard module:

Public Function RATE1(myArray As Excel.Range, s1 As String, s2 As
String, myDate As Long) As Double
With Application.WorksheetFunction
RATE1 = myArray.Cells(.Match(s1 & ":" & s2,
myArray.Columns(1), 0), _
.Match(myDate, myArray.Rows(1), 0))
End With
End Function

Usage: =RATE1(A1:F5,"OUT","EE",1992)

A1:F5 is your data range (the year and IN/OUT EE/ER stuff). It uses
the first row of the range to look for year, and the first column of
the range to look for IN/OUT and EE/ER.


On Aug 30, 8:30 am, Stu Gnu wrote:
I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

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%