Function CatValue(pVal As String) As Range

If pVal = Worksheets("Sheet3").Range("A2").Value Then
Set CatValue = Range("Rangename1")

ElseIf pVal = Worksheets("Sheet3").Range("A3").Value Then
Set CatValue = Range("Rangename2")

Else
CatValue = "0"
End If

End Function


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"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