cerca verticale dal basso verso l'alto!

Testo nascosto - clicca qui

Function NLookup(Dove As Range, Valore As Variant, Occorrenza As Long, riga As Long, colonna As Long) As Variant
Dim Contatore As Integer
Dim CellaRicerca As Range
Dim First As Range
Dim CCorrente As Range
Dim Trovato As Boolean

Contatore = 0
Trovato = False
For Each CCorrente In Dove
If CCorrente.Value = Valore Then
Contatore = Contatore + 1
End If
If Contatore = Occorrenza Then
Trovato = True
Exit For
End If
Next
If Trovato Then
NLookup = CCorrente.Offset(riga, colonna).Value
Else
NLookup = CVErr(xlErrNA)
End If
End Function

Function ReverseLookup(Dove As Range, Valore As Variant, colonna As Long) As Variant
Dim CellaRicerca As Range
Dim Indice As Long
Dim CCorrente As Range
Dim Trovato As Boolean
Dim CCH As Integer
Dim CCW As Integer

Contatore = 0
Trovato = False
CCH = Dove.Columns(Dove.Columns.Count).Column - Dove.Column + 1
CCW = Dove.Rows(Dove.Rows.Count).Row - Dove.Row + 1
If colonna > CCH Then
ReverseLookup = CVErr(xlErrNA)
Else
Indice = Dove.Rows(Dove.Rows.Count).Row
While Indice > Dove.Row And Not Trovato
If Cells(Indice, Dove.Column).Value = Valore Then
Trovato = True
End If
Indice = Indice - 1
Wend
End If
If Trovato Then
ReverseLookup = Cells(Indice + 1, Dove.Column + colonna - 1).Value
Else
ReverseLookup = CVErr(xlErrNA)
End If
End Function