RECHERCHEV entre deux classeurs : guide étape par étape facile

Si vous travaillez avec plusieurs classeurs dans Excel, vous devrez peut-être utiliser la fonction VLOOKUP pour récupérer des données d'un classeur à un autre.

La fonction VLOOKUP recherche une valeur spécifique dans une table d'un classeur séparé et renvoie une valeur correspondante à partir de la même ligne. La syntaxe doit inclure une référence complète au deuxième classeur et au nom de la feuille de calcul.

Obtenir les bonnes références peut être difficile lorsque vous utilisez la fonction pour la première fois. Cet article utilise des exemples de feuilles de calcul pour vous montrer exactement comment saisir les formules.

Vous apprendrez également certaines améliorations apportées à VLOOKUP et plusieurs alternatives à la fonction.

Commençons!

Table des matières

• Qu'est-ce que VLOOKUP ?
• Comment utiliser VLOOKUP entre deux classeurs
  • Exemple de données
  • Étape 1 : Gardez les deux classeurs ouverts
  • Étape 2 : Commencez à saisir la formule VLOOKUP
  • Étape 3 : Comment référencer le deuxième classeur
• 3 erreurs courantes de VLOOKUP et comment les corriger
  • 1. Erreur #N/A
  • 2. #Réf ! Erreur de numéro de colonne
  • 3. Erreur entre guillemets et crochets
• 4 conseils pour gérer différents classeurs
• Comment mettre à jour les données automatiquement
• Utilisations avancées de la fonction VLOOKUP
• 2 principales alternatives à VLOOKUP
  • 1. CORRESPONDANCE D'INDICE
  • 2. XRECHERCHE
• Comment utiliser VLOOKUP sur plusieurs feuilles
  • Étape 1 : Identifiez les composants
  • Étape 2 : Configurez vos données
  • Étape 3 : Utilisez la fonction VLOOKUP
  • (Facultatif) Étape 4 : Résoudre les erreurs #N/A
• Dernières pensées

Qu'est-ce que VLOOKUP ?

Supposons que vous ayez un classeur de ventes de produits et un classeur séparé avec les prix de chaque produit. Vous souhaitez accéder aux prix du deuxième classeur et les utiliser dans les calculs du premier classeur.

VLOOKUP est une solution pour cette tâche. Le terme signifie "Recherche verticale . "

Il s'agit d'une fonction intégrée qui vous permet de rechercher une valeur spécifique dans une colonne et de renvoyer une valeur correspondante dans une autre colonne en fonction de l'emplacement.

Il peut être utilisé avec :

• une seule feuille de calcul

• plusieurs feuilles dans le même classeur

• deux classeurs Excel

Si vous n'avez pas la complexité supplémentaire de plusieurs classeurs, consultez ce didacticiel sur la comparaison de deux colonnes dans Excel à l'aide de VLOOKUP .

Comment utiliser VLOOKUP entre deux classeurs

La fonction VLOOKUP est particulièrement utile lorsque vous devez rechercher des données dans plusieurs feuilles de calcul ou classeurs.

Les trois principales étapes pour y parvenir sont les suivantes :

1. Ouvrez les deux classeurs.

2. Entrez la formule VLOOKUP dans le premier classeur Excel.

3. Fournissez une référence pleinement qualifiée au deuxième classeur.

Exemple de données

Ce didacticiel simple fonctionne à travers des exemples basés sur des exemples de données. Pour continuer, créez un nouveau classeur appelé "Ventes" et entrez ces données dans les colonnes A et B :

• Pommes, 40

• Oranges, 30

• Poires, 70

• Bananes, 20

Ajoutez une troisième colonne pour contenir les informations sur le prix :

Créez un deuxième classeur Excel appelé "Produit" et renommez Sheet1 en "Prix". Entrez ces données dans la colonne A et la colonne B :

• Pommes, 1,50

• Oranges, 2.30

• Poires, 3.60

• Bananes, 2.10

Les données ressemblent à ceci :

Pour calculer les revenus des ventes dans la première feuille de calcul, nous devons rechercher le prix dans la deuxième feuille de calcul. VLOOKUP à la rescousse !

Étape 1 : Gardez les deux classeurs ouverts

Les deux classeurs doivent être ouverts lors de l'utilisation de la formule VLOOKUP entre deux classeurs. Si le deuxième classeur est fermé, la fonction renverra une erreur.

Vous pouvez soit les ouvrir séparément, soit utiliser la commande "Ouvrir" dans Excel pour les ouvrir tous les deux en même temps. Il existe deux façons d'ouvrir l'un ou l'autre des classeurs :

• Allez dans l'onglet Fichier et sélectionnez "Ouvrir".

• Utilisez le raccourci clavier "Ctrl + O" .

Une fois que les deux classeurs sont ouverts, vous pouvez basculer entre eux en cliquant sur l'onglet approprié en bas de la fenêtre Excel.

Étape 2 : Commencez à saisir la formule VLOOKUP

Voici la syntaxe de base de la fonction RECHERCHEV :

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : la valeur que vous souhaitez rechercher dans l'autre classeur.

• table_array : la plage de cellules de l'autre classeur qui contient les données que vous souhaitez récupérer.

• col_index_num : Le numéro de colonne dans la plage qui contient les données que vous souhaitez récupérer.

• range_lookup : Ce dernier paramètre est facultatif. Si vous le définissez sur TRUE ou si vous l'omettez, VLOOKUP recherchera une correspondance approximative. Si vous le définissez sur FALSE, VLOOKUP ne recherchera qu'une correspondance exacte.

Supposons que vous souhaitiez rechercher le prix des pommes dans le deuxième classeur de nos exemples de données. Voici comment la formule se décomposera :

• La lookup_value se trouve dans la cellule A2 du premier classeur.

• Le tableau de table se trouve dans la plage de cellules A1: B5 du deuxième classeur.

• Le col_index_num est la deuxième colonne qui contient le prix.

La partie la plus compliquée de la formule RECHERCHEV référence correctement le deuxième classeur.

Étape 3 : Comment référencer le deuxième classeur

Pour compléter la formule VLOOKUP pour deux classeurs, vous devrez utiliser une référence complète qui inclut ces détails du deuxième classeur :

• nom du classeur

• nom de la feuille

• plage de cellules

Cela garantit qu'Excel sait exactement où trouver les données que vous recherchez. La référence entièrement qualifiée est utilisée pour l'argument table_array de la fonction VLOOKUP.

Voici les valeurs des exemples de données :

• nom du classeur : Product.xlsx

• nom de la feuille : Tarifs

• plage de cellules : $A1:$B5

Pour rechercher le prix du premier article, saisissez la formule suivante dans la cellule C2 du premier classeur :

=RECHERCHEV(A2, [Produit.xlsx]Prix!$A$1:$B$5, 2, FAUX)

Cette image montre la fonction en action dans la barre de formule pour la première cellule :

Notez que le nom du classeur est entre crochets et que le nom de la feuille est suivi d'un point d'exclamation.

Si le nom du fichier Excel contient un espace ou des caractères spéciaux, vous devez placer l'intégralité du classeur et de la feuille entre guillemets simples.

La formule utilise des valeurs absolues pour la plage de cellules (le préfixe $). Cela vous permet de copier la formule dans le reste des lignes en cliquant sur la poignée de recopie.

Voici les résultats RECHERCHEV :

Et c'est tout! Vous savez maintenant comment utiliser VLOOKUP efficacement. Pour vous aider au cas où vous rencontreriez des erreurs, nous avons compilé trois des erreurs VLOOKUP les plus courantes dans la section suivante.

3 erreurs courantes de VLOOKUP et comment les corriger

Maintenant que vous avez appris à utiliser VLOOKUP à partir d'un autre classeur, vous pouvez rencontrer des erreurs courantes.

Voici les trois erreurs les plus fréquentes et comment les corriger.

1. Erreur #N/A

L'erreur #N/A se produit lorsque la valeur de recherche est introuvable dans le tableau de la table. Pour corriger cette erreur, vous pouvez essayer ce qui suit :

• Vérifiez l'orthographe de la valeur de recherche.

• Assurez-vous que la valeur de recherche se trouve dans la première colonne du tableau de la table.

• Vérifiez que la valeur de recherche n'est pas formatée en tant que texte dans les deux feuilles.

• Assurez-vous que l'argument range_lookup est défini sur FALSE pour trouver une correspondance exacte.

L'exemple ci-dessus n'a pas réussi à trouver une valeur correspondante pour les cerises dans le deuxième classeur.

2. #Réf ! Erreur de numéro de colonne

Le # Réf ! Une erreur se produit lorsque l'argument col_index_num est supérieur au nombre de colonnes dans le tableau de la table.

Pour corriger cette erreur, vérifiez que l'argument col_index_num est inférieur ou égal au nombre de colonnes de la table source.

Dans l'image ci-dessous, le col_index_num a été défini sur 3 mais il n'y a que deux colonnes dans la plage de référence.

3. Erreur entre guillemets et crochets

L'erreur entre guillemets et crochets se produit lorsque vous effectuez RECHERCHEV avec une valeur contenant des guillemets ou des crochets.

Pour corriger cette erreur, vous pouvez essayer ce qui suit :

• Utilisez des guillemets doubles autour de la valeur de recherche.

• Utilisez la fonction CHAR(34) pour insérer des guillemets doubles.

• Utilisez la fonction SIERREUR pour renvoyer une cellule vide si la valeur de recherche contient des guillemets ou des crochets.

Une fois que vous êtes familiarisé avec ces erreurs, les corriger est simple ! Pour vous aider à devenir un pro de RECHERCHEV, nous avons fourni quatre conseils pour gérer différents classeurs dans la section suivante.

4 conseils pour gérer différents classeurs

Lorsque vous travaillez avec plusieurs classeurs, il est important de savoir quel classeur contient quelles données.

Voici nos quatre meilleurs conseils pour garder votre santé mentale :

1. Utilisez des conventions de dénomination pour vous aider à vous rappeler quelles feuilles de calcul Excel contiennent quelles données.

2. Utilisez des références absolues dans vos formules pour vous assurer que la plage de recherche fait référence aux cellules correctes même si vous déplacez ou copiez la formule vers un autre emplacement ou une autre feuille Excel.

3. Utilisez la fonction SIERREUR pour gérer les erreurs #N/A qui peuvent se produire si les valeurs de recherche sont introuvables dans les données source.

4. Envisagez de verrouiller les cellules du classeur de référence afin qu'elles ne puissent pas être écrasées.

Comment mettre à jour les données automatiquement

Lorsque vous travaillez avec plusieurs fichiers Excel, la mise à jour manuelle des données peut prendre du temps .

Si vous souhaitez que votre formule RECHERCHEV se mette à jour automatiquement lorsque vous ajoutez ou supprimez des données des autres classeurs, vous pouvez utiliser la fonction INDIRECT.

Voici comment:

• Dans la formule VLOOKUP, remplacez l'argument de tableau de table par la fonction INDIRECT.

• Dans la fonction INDIRECT, utilisez une référence à la cellule qui contient le tableau tableau dans l'autre classeur.

Pour y parvenir avec nos exemples de classeurs, suivez ces étapes.

1. Utilisez une cellule de la colonne D pour saisir la référence à la feuille "Prix" du classeur "Produit" et la plage de cellules A1:B5 comme ceci : [Product.xlsx]Prices!A:B

2. Dans la cellule C2 du classeur "Sales", utilisez cette formule : =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. Copiez la formule vers le bas (les références absolues garantissent que la cellule avec la feuille de calcul est conservée).

Ces actions garantissent que les modifications apportées à la feuille d'origine sont automatiquement mises à jour dans les cellules connectées.

Cette image montre la fonction INDIRECT combinée avec VLOOKUP :

Notez que cet exemple utilise la plage "A:B" pour référencer les deux colonnes. Cela signifie que lorsque vous ajoutez plus de produits et de prix, la formule continuera à fonctionner dans le classeur actuel.

Maintenant que vous avez un avant-goût de la puissance de VLOOKUP, jetons un coup d'œil à quelques utilisations avancées de la fonction dans la section !

Utilisations avancées de la fonction VLOOKUP

Les exemples de cet article renvoient une seule correspondance pour chaque valeur de recherche. Si vous souhaitez travailler avec plusieurs correspondances d'un classeur différent, vous pouvez utiliser une formule matricielle à la place.

Vous pouvez également combiner VLOOKUP avec d'autres fonctions. Par exemple, vous pouvez l'intégrer dans une fonction SI pour une analyse complexe supplémentaire.

L'analyse complexe comprend des techniques telles que les tests du chi carré et les tests T d'échantillons appariés. Voici un exemple d'utilisation du test du chi carré dans Excel :

Les utilisateurs avancés tirent parti de VLOOKUP pour comparer des ensembles de données et identifier les incohérences. Vous trouverez d'autres moyens dans cet article pour trouver des écarts dans Excel .

Mais que se passe-t-il si vous ne souhaitez pas utiliser VLOOKUP pour une raison quelconque ? Tu es chanceux! Il existe des alternatives, ce que nous aborderons dans la section suivante.

2 principales alternatives à VLOOKUP

Lorsqu'il s'agit de rechercher des valeurs dans différentes feuilles, VLOOKUP est souvent la fonction incontournable. Cependant, il existe des alternatives qui peuvent être utiles dans certaines situations.

Les deux plus courants sont :

• CORRESPONDANCE D'INDICE

• XRECHERCHE

1. CORRESPONDANCE D'INDICE

INDEX et MATCH est une combinaison puissante de deux fonctions qui peuvent être utilisées pour rechercher des valeurs dans une table. Contrairement à VLOOKUP, qui nécessite que la valeur de recherche se trouve dans la colonne la plus à gauche de la table, INDEX MATCH peut rechercher des valeurs dans n'importe quelle colonne.

Voici comment cela fonctionne:

1. La fonction INDEX renvoie une valeur à partir d'une plage spécifiée en fonction d'un numéro de ligne et de colonne.

2. La fonction MATCH renvoie la position d'une valeur de recherche dans une plage spécifiée.

Pour utiliser INDEX MATCH, vous devez spécifier la plage que vous souhaitez rechercher, la ligne dans laquelle vous souhaitez rechercher la valeur de recherche et la colonne dans laquelle vous souhaitez renvoyer le résultat.

Voici un exemple de formule :

=INDEX(TableRange,MATCH(LookupValue,LookupRange,0),ColumnIndex)

Pour l'utiliser avec les exemples de feuilles de calcul, voici la formule :

=INDEX([Produit.xlsx]Prix !$B$1 :$B$5, MATCH(A2, [Produit.xlsx]Prix !$A$1 :$A$5, 0))

Cela se décompose en :

• La fonction MATCH recherche la valeur dans la cellule A2 (nom du produit) dans la plage A1:A5 dans la feuille "Prix" du classeur "Produit".

• La fonction INDEX renvoie une valeur de la plage B1: B5 dans la feuille "Prices" du classeur "Product", en fonction du numéro de ligne fourni par la fonction MATCH.

Cette image montre la formule en action :

2. XRECHERCHE

XLOOKUP est une fonction plus récente qui a été introduite dans Excel 365 . Il est similaire à VLOOKUP, mais avec quelques fonctionnalités supplémentaires.

L'un des principaux avantages de XLOOKUP est qu'il peut rechercher des valeurs à gauche de la colonne de recherche.

Voici comment cela fonctionne:

1. La fonction XLOOKUP renvoie une valeur d'une plage spécifiée en fonction d'une valeur de recherche.

2. Vous pouvez spécifier si vous voulez une correspondance exacte ou une correspondance approximative.

3. Vous pouvez également spécifier ce qu'il faut renvoyer si aucune correspondance n'est trouvée.

Pour utiliser XLOOKUP, vous devez spécifier la valeur de recherche, la plage que vous souhaitez rechercher et la colonne dans laquelle vous souhaitez renvoyer le résultat.

Voici un exemple de formule :

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"Not Found",0)

Pour l'utiliser avec les exemples de feuilles de calcul, voici la formule :

=XLOOKUP(A2, [Produit.xlsx]Prix !$A$1 : $A$5, [Produit.xlsx]Prix !$B$1 :$B$5)

Cela se décompose en :

• A2 : La valeur que vous souhaitez rechercher (nom du produit).

• [Product.xlsx]Prices !$A$1:$A$5 : la plage dans laquelle vous souhaitez rechercher la valeur de recherche (noms de produits dans la feuille "Prix" du classeur "Produit").

• [Product.xlsx]Prices!$B$1:$B$5 : La plage qui contient les valeurs de retour (prix dans la feuille « Prix » du classeur « Produit »).

Voici la formule en action :

Comment utiliser VLOOKUP sur plusieurs feuilles

Si vous devez effectuer une RECHERCHEV entre deux classeurs, vous devrez peut-être également utiliser RECHERCHEV sur plusieurs feuilles.

Heureusement, le processus pour le faire est très similaire à l'exécution d'une RECHERCHEV entre deux classeurs.

Il y a quatre étapes.

Étape 1 : Identifiez les composants

Plutôt que d'inclure le tableau comme vous le feriez pour une feuille, vous devrez également inclure le nom de la feuille.

Par exemple, si vous souhaitez rechercher une valeur dans la plage A2:B6 sur la feuille Jan du classeur Sales_reports.xlsx, votre formule ressemblera à ceci :

=VLOOKUP(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2,FALSE)

Étape 2 : Configurez vos données

Assurez-vous que vos données sont organisées de manière à faciliter leur référencement sur plusieurs feuilles.

Vous pouvez envisager de créer différents onglets pour des données de référence spécifiques.

Étape 3 : Utilisez la fonction VLOOKUP

Une fois vos données configurées, vous pouvez utiliser la fonction VLOOKUP pour rechercher et récupérer des données à partir d'autres feuilles.

Assurez-vous simplement d'inclure le nom de la feuille dans votre formule, comme indiqué ci-dessus.

(Facultatif) Étape 4 : Résoudre les erreurs #N/A

Si vous rencontrez des erreurs #N/A lors de l'utilisation de RECHERCHEV sur plusieurs feuilles, revérifiez votre formule pour vous assurer que vous avez correctement référencé toutes les feuilles et plages nécessaires.

Vous pouvez également vérifier que vous effectuez une correspondance exacte, plutôt qu'une recherche de plage.

Dernières pensées

Vous avez appris à utiliser VLOOKUP pour rechercher et récupérer des données dans deux feuilles de calcul. Cette fonction polyvalente facilite la recherche de valeurs dans un classeur en fonction des valeurs correspondantes dans un autre.

La puissance de la fonction VLOOKUP s'étend bien au-delà des feuilles de calcul simples, atteignant différents classeurs pour lier et analyser les données.

La maîtrise de cette fonction est une étape importante vers la maîtrise d'Excel, car elle ouvre de nouvelles possibilités de gestion et d'interprétation de vos données.

En incorporant des fonctions telles que INDIRECT, INDEX-MATCH ou XLOOKUP, vous pouvez encore améliorer la flexibilité et l'efficacité de vos recherches de données.

Continuez à pratiquer et à explorer les vastes capacités d'Excel ; ce n'est que la pointe de l'iceberg. Bonne chasse aux données !