Not tested, but try this

Function GetValue(filename)
Dim mtchValue
Dim sh As String

sh = "'[" & filename & "]May'!"
mtchValue = Application.Evaluate( _
"MATCH(1,(" & sh & "A1:A10000=20)*" & _
"(" & sh & "B1:B10000=6)*" & _
"(" & sh & "C1:C10000=""F"")*" & _
"(" & sh & "E1:E10000=""Escada""),0))")
If Not IsError(mtchValue) Then
GetValue = Application.Evaluate("Index(" & sh & "F1:F10000," &
mtchValue & ")")
End If

End Function


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Jeff" wrote in message
...
Hello,

I have the following hardcoded function in a worksheet:


=IF(ISNA(MATCH(1,('[Option 11 CSV.xls]May'!A1:A10000=20)*('[Option 11
CSV.xls]May'!B1:B10000=6)* ('[Option 11
CSV.xls]May'!C1:C10000="F")*('[Option
11 CSV.xls]May'!E1:E10000="Escada"),0)),0,INDEX('[Option 11
CSV.xls]May'!F1:F10000,MATCH(1,('[Option 11
CSV.xls]May'!A1:A10000=20)*('[Option 11
CSV.xls]May'!B1:B10000=6)*('[Option
11 CSV.xls]May'!C1:C10000="F")*('[Option 11
CSV.xls]May'!E1:E10000="Escada"),0)))


I urgently need to have this function as a VBA procedure, it needs to be
included in an existing VBA macro. Also, the name of file (Option 11
CSV.xls)wil change, so can we have the option of selecting the file to

--
Regards,
Jeff