=ЕСЛИОШИБКА(ИНДЕКС(Лист1!A$9:A$82;НАИМЕНЬШИЙ(ЕСЛИ(Лист1!B$9:B$82=Лист1!K$1;СТРОКА(Лист1!B$9:B$82));СТРОКА(A9)-СТРОКА($A$8))-СТРОКА($A$8));"")

 Public Function ВБРУСЛОВ(диапазон As Range, диапазон_условий As Range, условие As Range, нчлдиапазона As Range)
Dim n As Long
Dim i As Long
Dim c As Variant
Dim a As Long
Dim b As Long
Dim m As Long
Dim k As Long
Set chRng = диапазон
Set condRng = диапазон_условий
Set term = условие
Set begin = нчлдиапазона
n = chRng.Rows.Count
b = begin.Row
a = Application.ThisCell.Row
If a = b Then
m = 0
k = 0
Else
m = a - b
k = a - b - 1
End If
For i = 1 To n
c = chRng(i).Value
Set s = Application.ThisCell.Offset(-m, 0).Resize(Application.ThisCell.Rows.Count + k, Application.ThisCell.Columns.Count).Find(What:=c, LookIn:=xlValues)
If condRng(i).Value = term.Value And s Is Nothing Then
ВБРУСЛОВ = chRng(i).Value
Exit Function
End If
Next i

End Function

Private Sub Worksheet_Calculate()
If ThisWorkbook.Worksheets("Сценарии".Range("Scenario_Current" .Value <> If ThisWorkbook.Worksheets("Сценарии" .Range("CurrentCase".Value then
Application.EnableEvents = False
ThisWorkbook.Worksheets("Предпосылки".Range("PropertyTax".Value = ThisWorkbook.Worksheets("Сценарии".Range("PropertyTaxScen".Value
ThisWorkbook.Worksheets("Предпосылки".Range("IncomeTax".Value = ThisWorkbook.Worksheets("Сценарии".Range("IncomeTaxScen".Value
Application.EnableEvents = True
End if
End Sub