Array formula

Hello,

A lead with a personal function.

Copy this code in a standard module

Function ArrayFormula(Cell1 As Range, Cell2 As Range) As String
Dim i&
Dim j&
Dim k&
Dim A$
Dim Animals
Application.Volatile
If Cell2 < 1 Or Cell2 > 7 Then
  ArrayFormula = vbNullChar
  Exit Function
End If
Animals = Array("Cats", "Dogs", "Birds")
For j& = LBound(Animals) To UBound(Animals)
  If Cell1 = Animals(j&) Then
    A$ = Cell1
    k& = 0
    For i& = (j& + 1) * 2 - 1 To (j& + 1) * 2
      k& = k& + 1
      If Cell2 = i& Then
        ArrayFormula = A$ & " " & k&
        Exit Function
      End If
    Next i&
  End If
Next j&
If A$ = vbNullString Then
  ArrayFormula = "Fish"
Else
  ArrayFormula = A$
End If
End Function

ArrayFormula(Cell1 As Range, Cell2 As Range)
Cell1 as range of animal's name
Cell2 as range of number

Example :
in A1 Cats, in B1 2 , in C1 =ArrayFormula(A1;B1) => Cats 2

With regards.

PMO
Patrick Morange