Barb

many thanks for your help. I am having a little trouble getting it to run,
but I think it's me rather than your code. I will come back to you (if I
may), if I need a little more help.

Stuart

"Barb Reinhardt" wrote:

Let's assume the following:

Your table is on a worksheet called "LookUpTable"
OUT:EE, etc is in Column 1
Year is in Row 1

Press CTRL F11
Insert a module.
Paste this into that module


Function RATE1(INorOUTString As String, EEorERString As String, LookUpYear
As Long) As Variant
Dim myRow As Long
Dim myCol As Long
Dim myWS As Worksheet
Dim myString As String

On Error Resume Next
Set myWS = ThisWorkbook.Sheets("LookUpTable")
On Error GoTo 0
If myWS Is Nothing Then
RATE1 = "No Lookup Table"
Else
myString = INorOUTString & ":" & EEorERString
Debug.Print myString
myRow = WorksheetFunction.Match(myString, myWS.Range("A:A")) '<~~change
row as needed
Debug.Print myRow
myCol = WorksheetFunction.Match(LookUpYear, myWS.Range("1:1"))
'<~~change col as needed
If myRow 0 And myCol 0 Then
RATE1 = myWS.Cells(myRow, myCol).Value
Else
RATE1 = "No matching row or column"
End If
End If

End Function

Enter something like this in a cell to get the data you need.

=rate1("IN","EE",1992)

--
HTH,
Barb Reinhardt



"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%