Valeur cible

=RECHERCHEV(J2;$S$2:$U$7;2;VRAI)*(ARRONDI.INF((J2/O2);-1)-RECHERCHEV(J2;$S$2:$U$7;1;VRAI))+RECHERCHEV(J2;$S$2:$U$7;3;VRAI)

=ARRONDI((N2+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;2;VRAI)*(ARRONDI.INF((ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2)/O2);-1)-RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;1;VRAI))+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;3;VRAI)-G2-F2)/0,91;2)

EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)

=Sal_base(P2;F2;G2;Q2)

Option Explicit
Const TxSS = 0.09       'taux sécurité sociale
Const Tranche1 = 10000  'borne 1ère tranche
Const Tranche2 = 30000  'borne 2ème tranche
Const Tranche3 = 120000 'borne 3ème tranche
Const Tx1 = 0.2         'taux 1ère tranche
Const Tx2 = 0.3         'taux 2ème tranche
Const Tx3 = 0.35        'taux 3ème tranche
Const AbtMin = 1000     'Abattement minimum
Const AbtMax = 1500     'Abattement maximum
Const AbtTx = 0.4       'taux de calcul de l'abattement

Function Sal_base(sal_net_cible As Currency, Panier As Currency, Transport As Currency, Pirg As Double)
'calcule le salaire de base à partir d'un salaire net cible, de primes de panier et de transport
'Assiette et Pirg sont là pour calculer IRG à la dizaine inférieure
'méthode dichotomique limitée à 50 boucles pour trouver une solution
'la borne inférieure est initialisée à sal_net_cible - Panier - Transport
'la borne supérieure est initialisée à (1 + Tx3) * sal_net_cible

    Dim i As Integer, bInf As Currency, bSup As Currency, Base_imposable As Currency
    Dim Sal_imposable As Currency, IRG As Currency, Abattement As Currency, Sal_dicho As Currency
    Dim sal_net As Currency, k As Integer
    If sal_net_cible > 0 And Panier > 0 And Transport > 0 And Pirg > 0 Then
        sal_net_cible = Round(sal_net_cible, 2)
        bInf = sal_net_cible - Panier - Transport
        bSup = (1 + Tx3) * sal_net_cible: Sal_dicho = -1
        k = 0
        
        While (Abs(sal_net_cible - sal_net) >= 0.01 And k < 50)
            k = k + 1
            Sal_dicho = Round((bSup + bInf) / 2, 2)
            Sal_imposable = Round(Sal_dicho * (1 - TxSS) + Panier + Transport, 2)
            Base_imposable = WorksheetFunction.RoundDown(Sal_imposable / Pirg, -1)
            IRG = Round(-(Base_imposable > Tranche1) * Tx1 * (Base_imposable - Tranche1) _
                        - (Base_imposable > Tranche2) * (Tx2 - Tx1) * (Base_imposable - Tranche2) _
                        - (Base_imposable > Tranche3) * (Tx3 - Tx2) * (Base_imposable - Tranche3), 2)
            Abattement = WorksheetFunction.Max(AbtMin, WorksheetFunction.Min(AbtMax, Round(Base_imposable * AbtTx, 2)))
            sal_net = Sal_imposable - IRG + Abattement
            If sal_net < sal_net_cible Then bInf = Round((bSup + bInf) / 2, 2) Else bSup = Round((bSup + bInf) / 2, 2)
    
        Wend
        
        If k < 50 Then
            Sal_base = Round((sal_net_cible + IRG - Abattement - Panier - Transport) / (1 - TxSS), 2)
        Else
            Sal_base = [na()]
        End If
    Else
        Sal_base = [na()]
    End If
End Function

Sub nouveauSalaire()
    Dim c As Range
    For Each c In [E2].Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1)
        If c.Offset(, 9) > 0 Then c.Offset(, 7).GoalSeek Goal:=c.Offset(, 9), ChangingCell:=c
    Next c
End Sub