=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99))-9)):ИНДЕКС(G:G;ПРОСМОТР(2;1/G4:G99;СТРОКА(G4:G99)))))*{1;-1};));G4:G99;1;))

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s$, x$, v As Variant
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        s = Target.Validation.Formula1
        v = Evaluate(s)
        If Left(s, 1) = "=" Then
            If TypeName(v) = "Range" Then v = v.Formula
            x = Join(Application.Transpose(v), ";")
        Else: x = s
        End If
        Select Case UBound(Split(";" & Mid(x, 1, InStr(1, x, Target) - 1), ";"))
            Case 1: Call Макрос_1
            Case 2: Call Макрос_2
            Case 3: Call Макрос_3
        End Select
        
        'ИЛИ
        'Application.Run "Макрос_" & UBound(Split(";" & Mid(x, 1, InStr(1, x, Target) - 1), ";"))
    End If
End Sub

=ПРОСМОТР(9^9;ВПР(Ч(ИНДЕКС(МАКС(ABS(ИНДЕКС(G:G;МАКС(СТРОКА(G4);ПРОСМОТР(9^9;G4:G99;СТРОКА(G4:G99))-9)):ИНДЕКС(G:G;ПРОСМОТР(9^9;G4:G99;СТРОКА(G4:G99)))))*{1;-1};));G4:G99;1;))

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s$, x$, v As Variant
    If Not Intersect(Target, Range("E3")) Is Nothing Then
        s = Target.Validation.Formula1
        v = Evaluate(s)
        If Left(s, 1) = "=" Then
            If TypeName(v) = "Range" Then v = v.Formula
            x = Join(Application.Transpose(v), ";")
        Else: x = s
        End If
        Application.Run "Лист1.Макрос_" & UBound(Split(";" & Mid(x, 1, InStr(1, x, Target) - 1), ";"))
    End If
End Sub

If Not Intersect(Target, Range("E3")) Is Nothing Then

if isempty(target(1, 1)) then call Макрос_0: Exit Sub

Sub Макрос1()
    Application.EnableEvents = 0
    MsgBox " Вводится значение  а ", vbInformation, " Сообщение"
    Range("E3:E4").Select
    ActiveCell.FormulaR1C1 = "а"

    MsgBox " Вводится значение  в ", vbInformation, " Сообщение"
    Range("E3:E4").Select
    ActiveCell.FormulaR1C1 = "в"

    MsgBox " Удаление данных ", vbInformation, " Сообщение"
    Range("E3:E4").Select
    ActiveCell.FormulaR1C1 = ""
    Application.EnableEvents = 1
End Sub

=ArrayFormula(ARRAY_CONSTRAIN(--iferror(B1:B=B2:B;);counta(B1:B);1))

=or(if(row()>1;B1=index(B:B;row()-1));B1=index(B:B;row()+1))

wd.Bookmarks("pismo55").Range.Text = [A1].Value

Private ver%
Function МЕТЦБР2#(Optional Code% = 2, Optional dDate As Date)
    Dim d As Object, ddate1 As Date, elem As Object:
    Set d = CreateObject("msxml2.domdocument." & MSXMLVersion & ".0")
    dDate = IIf(dDate, dDate, Date): ddate1 = Application.EDate(dDate, -1): d.async = 0
    d.Load ("https://www.cbr.ru/scripts/xml_metall.asp?date_req1=" & ddate1 & "&date_req2=" & dDate)
    Select Case ver
        Case Is > 3 'Установлен MSXML >= 4.0, используем XPath функцию last()
            МЕТЦБР2 = CDbl(d.SelectSingleNode("*/Record[@Code='" & Code & "'][last()]/Buy").Text)
        Case Else   'иначе используем IXMLDOMNodeList
            Set elem = d.SelectNodes("*/Record[@Code='" & Code & "']/Buy")
            МЕТЦБР2 = CDbl(elem(elem.Length - 1).Text)
    End Select
    Set d = Nothing
End Function
Private Function MSXMLVersion%()
    Dim arrVersions()
    If ver > 0 Then MSXMLVersion = ver: Exit Function
    With GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
        .EnumValues 2 ^ 31, "CLSID\{2933BF90-7B36-11D2-B20E-00C04F983E60}\VersionList\", arrVersions
    End With
    'номер старшей версии MSXML, установленной в системе
    MSXMLVersion = Split(arrVersions(0), ".")(0)
    ver = MSXMLVersion
End Function

=hyperlink("http://www.excelworld.ru";image("http://www.excelworld.ru/Ribbon/images/MicrosoftExcel.png"))

=ИНДЕКС(A:A;ПОИСКПОЗ(ПРОСМОТР(ОСТАТ(ОТБР(A1*24);24);{0:7:15:23};{1:2:3:1});ИНДЕКС(1:10;;ПОИСКПОЗ(ОТБР(A1);2:2));))

=B$1*A1/СУММ(A:A)