XL 2010 Recherche de valeurs dans un tableau variable

=Result($B:$B;"Date";C:C;"Code";30)

Function Result(colref As Range, ref$, coltxt As Range, txt$, h&)
Dim w As Worksheet, lig&, col%, i&, ligdeb&, maxi&, ordre&, n&
txt = txt & "*"
With Application.Caller
    Set w = .Parent
    lig = .Row
    col = .Column
End With
Result = ""
For i = lig To 1 Step -1
    If colref(i) = ref Then ligdeb = i: Exit For
Next
If ligdeb = 0 Then Exit Function
For i = ligdeb To ligdeb + h - 1
    If coltxt(i) Like txt Then maxi = maxi + 1
Next
ordre = 1
For i = lig - 1 To ligdeb Step -1
    If w.Cells(i, col) Like txt Then ordre = ordre + 1
Next
If ordre > maxi Then Exit Function
For i = ligdeb To ligdeb + h - 1
    If coltxt(i) Like txt Then n = n + 1: If n = ordre Then Result = coltxt(i): Exit Function
Next
End Function

Function Result(colref As Range, ref$, coltxt As Range, txt$, h&)
Dim i&, ligdeb&, n&, a$(), j%
txt = txt & "*"
Result = ""
'---recherche de la 1ère ligne---
For i = Application.Caller.Row To 1 Step -1
    If colref(i) = ref Then ligdeb = i: Exit For
Next i
If ligdeb = 0 Then Exit Function
'---recherche des dates---
For i = ligdeb + 1 To ligdeb + h - 1
    If IsDate(colref(i)) Then
        n = n + 1
        ReDim Preserve a(1 To 4, 1 To n)
        a(1, n) = Format(colref(i), "dddd dd-mmmm") 'format date à adapter
        a(1, n) = UCase(Left(a(1, n), 1)) & Mid(a(1, n), 2)
        For j = i - 1 To 1 Step -1
            If IsDate(colref(j)) Then Exit For
            If coltxt(j) Like txt Then
                a(2, n) = coltxt(j)
                a(3, n) = coltxt(j, 2)
                a(4, n) = coltxt(j, 3)
                Exit For
            End If
        Next j
    End If
Next i
If n Then Result = a 'matrice transposée
End Function

=SIERREUR(INDEX(Result($B:$B;"Date";$C:$C;"Code";30);COLONNES($H8:H8);LIGNES(H$8:H8));"")

If coltxt(j) Like txt Then

If coltxt(j, 1) Like txt Or coltxt(j, 2) Like txt Or coltxt(j, 3) Like txt Then

=SIERREUR(INDEX(Result($B:$B;"Date";$C:$E;"Code";30);COLONNES($H8:H8);LIGNES(H$8:H8));"")

Function Result(plage As Range)
Dim tablo, a$(), i&, n&, j%
tablo = plage 'matrice, plus rapide
ReDim a(1 To Application.Count(plage.Columns(1)), 1 To 4)
For i = 1 To UBound(tablo)
    If IsDate(tablo(i, 1)) Then
        n = n + 1
        a(n, 1) = Format(tablo(i, 1), "dddd dd-mmmm") 'format date à adapter
        a(n, 1) = UCase(Left(a(n, 1), 1)) & Mid(a(n, 1), 2)
        For j = 2 To 4: a(n, j) = tablo(i, j): Next j
    End If
Next i
Result = a 'matrice
End Function

=SIERREUR(INDEX(Result($B$4:$E$27);LIGNES(H$8:H8);COLONNES($H8:H8));"")

Function Result(plage As Range, ligne&)
Dim tablo, a$(1 To 4), i&, n&, j%
tablo = plage 'matrice, plus rapide
For i = 1 To UBound(tablo)
    If IsDate(tablo(i, 1)) Then
        n = n + 1
        If n = ligne Then
            a(1) = Format(tablo(i, 1), "dddd dd-mmmm") 'format date à adapter
            a(1) = UCase(Left(a(1), 1)) & Mid(a(1), 2)
            For j = 2 To 4: a(j) = tablo(i, j): Next j
            Exit For
        End If
    End If
Next i
Result = a 'matrice ligne
End Function

=Result($B$4:$E$27;LIGNES(H$8:H8))

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'si aucune SpecialCell
For Each c In [B:B].SpecialCells(xlCellTypeConstants, 1)
    If IsDate(c) Then c = Application.Proper(Format(c, "dddd")) & vbLf & Format(c, "dd-mmmm")
Next
Application.EnableEvents = True 'réactive les évènements
End Sub

Function Result(plage As Range, ligne&)
Dim tablo, a$(1 To 4), i&, n&, j%
tablo = plage 'matrice, plus rapide
For i = 1 To UBound(tablo)
    If tablo(i, 1) <> "" Then
        n = n + 1
        If n = ligne Then
            a(1) = Replace(tablo(i, 1), vbLf, " ")
            For j = 2 To 4: a(j) = tablo(i, j): Next j
            Exit For
        End If
    End If
Next i
Result = a 'matrice ligne
End Function