Comment rechercher ou trouver des valeurs dans un autre classeur ?

Configurer des références de recherche avec VLOOKUP peut vite devenir déroutant, surtout lorsque vous modifiez fréquemment le chemin, le nom ou la feuille du fichier source. Dans ces situations, automatiser le processus de recherche à l’aide de VBA s’impose comme une solution bien plus fluide : elle vous permet d’effectuer des recherches même si le classeur source est fermé, tout en gérant automatiquement la sélection des plages et la récupération des données.

Suivez ces étapes pour utiliser VBA dans les recherches inter-classeurs :

1. Appuyez simultanément sur Alt + F11 pour ouvrir la fenêtre de l’éditeur Microsoft Visual Basic pour Applications.

2.Dans l’éditeur VBA, cliquez sur Insertion>Module, puis copiez-collez le code suivant dans la fenêtre du module :

VBA : Recherche verticale de données et Valeur de retour depuis un autre classeur fermé

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Détails importants :

• Le code renvoie la valeur correspondante située deux colonnes à droite de la plage de recherche. Par exemple, si vous sélectionnez la colonne B, les résultats s’affichent en colonne D.
• Si vous souhaitez que le résultat s’affiche dans une autre colonne, remplacez le nombre 2 dans xRg.Offset(0,2).Formulapar une autre valeur (par exemple,)1 pour la colonne suivante, 3 pour la troisième colonne à droite).
• Sélectionnez le classeur et la feuille de calcul appropriés lorsque vous y êtes invité ; le code utilisera toujours la première feuille du fichier sélectionné. Modifiez-le si votre feuille source n’est pas la première.
• Enregistrez toujours votre fichier avant d’exécuter des macros inconnues, car elles ne peuvent pas être annulées une fois lancées.

3. Exécutez la macro en appuyant sur la touche F5 ou en cliquant sur le bouton Exécuter. Une boîte de dialogue intitulée « Kutools pour Excel » s’ouvrira alors et vous invitera à sélectionner la plage de cellules dans laquelle vous souhaitez rechercher les valeurs.

4. Après avoir sélectionné votre plage, cliquez sur OK. Peu après, une autre boîte de dialogue apparaît, vous invitant à choisir le classeur source (même s’il est fermé). Parcourez les dossiers, sélectionnez le fichier approprié, puis cliquez sur Ouvrir pour confirmer.

Une fois la macro terminée, les valeurs correspondantes du classeur source seront renvoyées dans la colonne cible de votre feuille de calcul actuelle. Si certaines valeurs sont manquantes, vérifiez que les plages de valeurs de recherche dans votre feuille de calcul actuelle correspondent exactement à celles des données sources (la casse ainsi que les espaces en début et en fin de cellule sont pris en compte pour une correspondance exacte).

Avantages : Gère les classeurs fermés, évite les chemins de fichiers codés en dur dans les formules et offre une grande flexibilité pour sélectionner les fichiers sources à la volée.
Considérations : Les macros doivent être activées ; VBA peut ne pas fonctionner sur des feuilles protégées ou avec des données provenant de fichiers non Excel. Enregistrez votre classeur au format prenant en charge les macros (*.xlsm) si vous prévoyez d’utiliser cette méthode régulièrement.

En cas d’erreur, vérifiez qu’il n’y a aucune faute de frappe dans les noms des feuilles ou des fichiers, que la plage sélectionnée est appropriée et que les chemins des fichiers sont accessibles. Pour le débogage, envisagez d’exécuter le code ligne par ligne à l’aide de l’éditeur VBA.

Outre l’approche classique avec VLOOKUP et VBA, il existe d’autres méthodes pour effectuer des recherches inter-classeurs dans Excel. Elles peuvent être préférables dans certaines situations, notamment lorsque votre structure de données est différente, que vous préférez les formules aux macros, ou que vous avez besoin de plus de flexibilité (comme rechercher vers la gauche ou utiliser plusieurs critères).

Utilisation des fonctions INDEX et EQUIV entre classeurs

En combinant INDEX et EQUIV, vous pouvez rechercher des valeurs dans n’importe quelle direction — gauche, droite, haut ou bas — même dans un autre classeur. Cette approche s’avère particulièrement utile lorsque la colonne contenant les données à extraire ne se trouve pas à droite de la colonne de recherche, une limitation bien connue de VLOOKUP.

Scénario : Supposons que vous souhaitiez récupérer un prix à partir d’un autre classeur ouvert, où le nom du fruit ne se trouve pas nécessairement dans la première colonne.

1.Dans votre classeur de destination, sélectionnez la cellule où vous souhaitez afficher le résultat (par exemple, C2) et saisissez la formule ci-dessous (remplacez le classeur, la feuille et la plage selon vos besoins) :

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Appuyez sur Entrée. Copiez ensuite la formule ou étendez-la aux autres lignes en faisant glisser la poignée de recopie.

Explication des paramètres :

• [Price.xlsx]Sheet1!$B$1:$B$24 : la plage où les prix sont stockés.
• B2 : Le nom du fruit à rechercher.
• [Price.xlsx]Sheet1!$A$1:$A$24 : la plage dans laquelle rechercher votre valeur.
• Le 0 à la fin garantit une correspondance exacte.

Points forts : Fonctionne aussi bien pour les recherches vers la gauche que vers la droite ; s’adapte à des dispositions plus flexibles.
Conseils : Évitez de déplacer ou de renommer les fichiers sources sans mettre à jour votre formule.

Utilisation de XLOOKUP pour les recherches inter-classeurs (Excel 365 et versions ultérieures)

Si vous utilisez Excel 365 ou Excel 2021, la nouvelle fonction XLOOKUP est encore plus flexible. Elle vous permet de trouver facilement des correspondances exactes, de prendre en charge les recherches vers la gauche et de gérer automatiquement les valeurs manquantes sans générer d’erreurs.

Pour l’utiliser :
Dans la cellule où vous souhaitez le résultat, saisissez :

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Appuyez sur Entrée et copiez la formule selon vos besoins. Ici, « Non trouvé » peut être remplacé par tout texte personnalisé que vous souhaitez afficher en cas d’échec de la recherche.

Avantages : Plus flexible que VLOOKUP et plus simple à utiliser ; évite de nombreuses erreurs courantes liées aux anciennes formules. Toutefois, XLOOKUP n’est disponible que dans les versions récentes d’Excel.

Pour des besoins complexes, tels que la correspondance multicritères, la recherche dans des feuilles fusionnées ou l’optimisation des performances dans des fichiers très volumineux, envisagez d’organiser vos Données source sous forme de tables structurées ou d’utiliser l’outil Power Query d’Excel pour Lier les données efficacement entre plusieurs classeurs.