Function RECHERCHEVENS(ColonneValeur As Range, Critere1 As Variant, PlageRecherche1 As Variant, Critere2 As Variant, PlageRecherche2 As Variant, _
Optional Critere3 As Variant, Optional PlageRecherche3 As Variant, _
Optional Critere4 As Variant, Optional PlageRecherche4 As Variant, _
Optional Critere5 As Variant, Optional PlageRecherche5 As Variant)

'Auteur : www.Indexmatch.fr
'Date : 08/2021
'fonction de rechercheV avec plusieurs critères : minimum 2 critères / maximum 5 critères

    Dim R1 As Variant 'variable pour le numéro de colonne du critère 1
    Dim R2 As Variant 'variable pour le numéro de colonne du critère 2
    Dim RF As Variant 'variable pour le numéro de colonne du critère 3
    Dim R3 As Variant 'variable pour le numéro de colonne du critère 3
    Dim R4 As Variant 'variable pour le numéro de colonne du critère 4
    Dim R5 As Variant 'variable pour le numéro de colonne du critère 5
    
    Dim RS1 As String 'variable pour feuille du critère 1
    Dim RS2 As String 'variable pour feuille du critère 2
    Dim RSF As String 'variable pour feuille du critère 3
    Dim RS3 As String 'variable pour feuille du critère 3
    Dim RS4 As String 'variable pour feuille du critère 4
    Dim RS5 As String 'variable pour feuille du critère 5
    
    Dim counter As Integer 'variable de compteur
    Dim lastLine As Long 'variable pour définir la dernière ligne à traiter
    
    R1 = PlageRecherche1.Column
    R2 = PlageRecherche2.Column
    RF = ColonneValeur.Column
    
    RS1 = PlageRecherche1.Worksheet.Name
    RS2 = PlageRecherche2.Worksheet.Name
    RSF = ColonneValeur.Worksheet.Name
    
    counter = 0
    lastLine = Sheets(RS1).Cells(PlageRecherche1.Row, R1).End(xlDown).Row

' Avec 5 critères -----------

    If IsMissing(Critere5) = False And IsMissing(PlageRecherche5) = False Then
        
        R3 = PlageRecherche3.Column
        R4 = PlageRecherche4.Column
        R5 = PlageRecherche5.Column
        
        RS3 = PlageRecherche3.Worksheet.Name
        RS4 = PlageRecherche4.Worksheet.Name
        RS5 = PlageRecherche5.Worksheet.Name
    
        If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Or Critere4 = "" Or Critere5 = "" Then
        
            RECHERCHEVENS = "#VALEUR"
            
        Else
    
            Do While counter < lastLine
            
                counter = counter + 1
                
                If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _
                Sheets(RS4).Cells(counter, R4).Value = Critere4 And Sheets(RS5).Cells(counter, R5).Value = Critere5 Then
                            
                    If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                                
                        RECHERCHEVENS = "#N/A"
                                
                    Else
                            
                        RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                            
                    End If
                        
                End If
                    
            Loop
    
        End If
    
    End If
    
    '4 criteres -----------
    
    If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = False And IsMissing(PlageRecherche4) = False Then
        
        R3 = PlageRecherche3.Column 'optional
        R4 = PlageRecherche4.Column 'optional
        
        RS3 = PlageRecherche3.Worksheet.Name 'optional
        RS4 = PlageRecherche4.Worksheet.Name 'optional
        
        If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Or Critere4 = "" Then
        
            RECHERCHEVENS = "#VALEUR"
            
        Else
    
            Do While counter < lastLine
            
                counter = counter + 1
                
                If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _
                Sheets(RS4).Cells(counter, R4).Value = Critere4 Then
                            
                    If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                                
                        RECHERCHEVENS = "#N/A"
                                
                    Else
                            
                        RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                            
                    End If
                        
                End If
                    
            Loop
            
        End If
    
    End If
    
    ' Avec 3 critères -----------

    If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _
    IsMissing(Critere3) = False And IsMissing(PlageRecherche3) = False Then
        
        R3 = PlageRecherche3.Column
        
        RS3 = PlageRecherche3.Worksheet.Name
    
        If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Then
        
            RECHERCHEVENS = "#VALEUR"
            
        Else
    
            Do While counter < lastLine
            
                counter = counter + 1
                
                If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 Then
                            
                    If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                                
                        RECHERCHEVENS = "#N/A"
                                
                    Else
                            
                        RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                            
                    End If
                        
                End If
                    
            Loop
    
        End If
    
    End If
    
    ' Avec 2 critères -----------
    
    If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _
    IsMissing(Critere3) = True And IsMissing(PlageRecherche3) = True Then
    
        If Critere1 = "" Or Critere2 = "" Then
    
            RECHERCHEVENS = "#VALEUR"
        
        End If
        
        Do While counter < lastLine
        
            counter = counter + 1
            
            If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 Then
                        
                If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then
                            
                    RECHERCHEVENS = "#N/A"
                            
                Else
                        
                    RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value
                        
                End If
                    
            End If
                
        Loop
    
    End If
    
End Function