i forgot your case else statement:

Select Case pval
Case [Sheet1!A2]
CatValue = "Rangename1"
Case [sheet1!A3]
CatValue = "Rangename2"
Case Else
CatValue = "0"
End Select



--


Gary


"Alejandro Miron" wrote in message
...

Im trying to put this into VBA, so that I can chain more than 7 If's

In a cell I have

=INDEX(A5:A10,MATCH(A1,IF(A2="rangename",rangename ,0),0)) This statement
works

Im trying to substitute the If statement with a Function like this

=INDEX(A5:A10,MATCH(A1,CatValue(A2),0))

where catValue is the following function


Function CatValue(pVal As String) As String

If pVal = [sheet1!A2] Then
CatValue = "Rangename1"

ElseIf pVal = [sheet1!A3] Then
CatValue = "Rangename2"

Else
CatValue = "0"
End If

End Function


But The result I get is #value!

But when I test the function just displaying the result by putting
=CatValue(A2) in a cell it does give me the result = Rangename1

Thank you very much in advance

Alex