Comment utiliser la fonction RECHERCHEV dans Excel

Souhaitez-vous savoir comment utiliser le Fonction RECHERCHEV dans Excel? Dans ce didacticiel, nous allons vous montrer comment utiliser la fonction RECHERCHEV pour copier des données d'une autre feuille de calcul ou d'un classeur, rechercher V sur plusieurs feuilles et effectuer une recherche dynamique pour renvoyer les valeurs de différentes feuilles vers différentes cellules.

Lors d’une recherche d’informations dans Excel, il est rare que toutes les données se trouvent sur la même feuille. Le plus souvent, vous devrez effectuer des recherches dans plusieurs feuilles, voire dans différents classeurs. La bonne nouvelle est que Microsoft Excel propose plusieurs façons de procéder, et la mauvaise nouvelle est que tous les formulaires sont un peu plus compliqués qu'une formule RECHERCHEV standard. Mais avec un peu de patience, vous apprendrez.

Comment utiliser la fonction RECHERCHEV dans Excel entre deux feuilles

Pour commencer, nous allons poser un cas simple : utilisez la fonction RECHERCHEV dans Excel pour copier les données d'une autre feuille de calcul. Elle est très similaire à une formule RECHERCHEV normale qui recherche la même feuille de calcul. La différence est qu'il inclut le nom de la feuille dans l'argument table_array pour indiquer à votre formule dans quelle feuille de calcul se trouve la plage de recherche.

Ici, vous pouvez en apprendre davantage sur : Comment améliorer votre expérience Excel – 13 conseils utiles

La formule générique pour Fonction RECHERCHEV dans Excel d'une autre feuille est le suivant :

• RECHERCHEV (valeur_recherche, Feuille! Gamme, col_index_num, [range_lookup])

À titre d'exemple, extrayons les chiffres de ventes du rapport de janvier vers la feuille Résumé. Pour ce faire, vous devez définir les arguments suivants :

• Valeurs de recherche Ils sont dans la colonne A de la feuille Résumé et nous nous référons à la première cellule de données, qui est A2.
• Tableau_tableauest le rang A2: B6 sur la feuille de janvier. Pour la référencer, vous devez préfixer la référence de plage avec le nom de la feuille suivi du point d'exclamation : Jan! 2$A$ : 6$B$.

NOTE: Ici, vous devez faire attention à verrouiller la plage avec des références de cellule absolues pour éviter qu'elle ne change lorsque vous copiez la formule dans d'autres cellules.

• Col_index_numvaut 2 car vous souhaitez copier une valeur de la colonne B, qui est la deuxième colonne de la matrice du tableau.
• Range_lookupest défini sur FALSE pour trouver une correspondance exacte.

En mettant les arguments ensemble, vous obtenez cette formule :

• =RECHERCHEV(A2, janvier !$A$2 :$B$6, 2, FALSE)

Faites glisser la formule vers le bas de la colonne et vous obtiendrez ce résultat :

De même, vous pouvez utiliser la fonction Vlookup en utilisant les données des feuilles de février et mars :

• =RECHERCHEV(A2, février !$A$2 :$B$6, 2, FALSE)
• =RECHERCHEV(A2, mar!$A$2:$B$6, 2, FALSE)

Conseils et notes sur la fonction RECHERCHEV dans Excel :

• Si le nom de la feuille contient espaciosou des personnages non alphabétique, doit être placé entre guillemets simples, tels que « Soldes de janvier » ! 2$A$ : $B$6.
• Au lieu de saisir un nom de feuille directement dans une formule, vous pouvez basculer vers la feuille de calcul de recherche et y sélectionner la plage. Excel insérera automatiquement une référence avec la syntaxe correcte, vous évitant ainsi d'avoir à vérifier le nom et à résoudre les problèmes.

Méthodes pour utiliser la fonction RECHERCHEV dans Excel

Passons maintenant aux détails de l'utilisation de la fonction RECHERCHEV dans Excel. Ici, nous vous laissons quelques options :

Méthode 1 : Recherche V d'un autre classeur

Pour utiliser la fonction RECHERCHEV dans Excel entre deux classeurs, vous devez mettre le nom du fichier entre crochets, suivi du nom de la feuille et du point d'exclamation.

• Par exemple : pour trouver la valeur A2 dans la plage A2:B6 sur la feuille de janvier du classeur XLSX, utilisez cette formule :
• =RECHERCHEV(A2, [Sales_reports.xlsx]Jan!$A$2 :$B$6, 2, FALSE)

Méthode 2 : Vlookup sur plusieurs feuilles avec SIERREUR

Lorsque vous devez effectuer une recherche entre plus de deux feuilles, la solution la plus simple consiste à utiliser la fonction RECHERCHEV dans Excel en combinaison avec SIERREUR. L'idée est d'imbriquer plusieurs fonctions SIERREUR pour vérifier plusieurs feuilles de calcul une par une : si la première RECHERCHEV ne trouve pas de correspondance sur la première feuille, elle cherche sur la feuille suivante, et ainsi de suite. Par exemple:

• ERREUR (RECHERCHEV (…), SIERREUR (RECHERCHEV (…),…, » Pas trouvé"))

Pour voir comment cette approche fonctionne sur des données réelles, considérons l'exemple suivant :

Ci-dessous le tableau de Resumen que vous devez compléter avec les noms des articles et les quantités lors de la recherche du numéro de commande sur les fiches Ouest et Est :

• étape 1: Tout d'abord, vous allez retirer les éléments. Pour ce faire, on indique à la formule RECHERCHEV Retrouver le numéro de commande en A2 sur la feuille cette et renvoie la valeur de la colonne B (2ème colonne dans tableau_table A2:C6).
• étape 2: Si une correspondance exacte n'est pas trouvée, recherchez dans la feuille Ouest.
• étape 3: Oui les deux Crash de rechercheV, renverra le message : "Pas trouvé".
  • =SIERREUR(RECHERCHEV(A2, Est!$A$2:$C$6, 2, FALSE), SIERREUR(RECHERCHEV(A2, Ouest!$A$2:$C$6, 2, FALSE), «Introuvable»))

• étape 4: Pour renvoyer le montant, changez simplement le numéro d'index de la colonne en 3 :
  • =SIERREUR(RECHERCHEV(A2, Est!$A$2:$C$6, 3, FALSE), SIERREUR(RECHERCHEV(A2, Ouest!$A$2:$C$6, 3, FALSE), «Introuvable»))

Conseil: Si nécessaire, vous pouvez spécifier différents tableaux de tables pour différentes fonctions RECHERCHEV. Dans cet exemple, les deux feuilles de recherche ont le même nombre de lignes (A2:C6), mais vos feuilles de calcul peuvent avoir une taille différente.

Méthode 3 : Vlookup sur plusieurs classeurs

Pour effectuer une recherche entre deux ou plusieurs classeurs, vous devez mettre le nom du classeur entre crochets et le placer avant le nom de la feuille.

• Par exemple- Voici comment utiliser Vlookup dansdeux fichiers différents (Livre1 et Livre2) avec une seule formule :
  • =SIERREUR(RECHERCHEV(A2, [Book1.xlsx]Est!$A$2:$C$6, 2, FALSE), SIERREUR(RECHERCHEV(A2, [Book2.xlsx]Ouest!$A$2:$C$6, 2, FAUX),»Pas trouvé»))

Méthode 4 : rendre le numéro d'index de colonne dynamique pour plusieurs colonnes Vlookup

Dans une situation où vous devez renvoyer des données de plusieurs colonnes, ayez col_index_num Soyez dynamique pourrait vous faire gagner du temps. Vous devez faire quelques ajustements :

• Pour l'argument col_index_num, utilisez la fonction COLONNES qui renvoie le nombre de colonnes dans un tableau spécifié : COLONNES ($A$1:B$1). (La coordonnée de la ligne n'a pas vraiment d'importance, il peut s'agir de n'importe quelle ligne.)
• Dans l'argument lookup_value, verrouille la référence de colonne avec le Signe $ ($A2), il reste donc fixe lors de la copie de la formule dans d'autres colonnes.

En conséquence, vous obtiendrez une sorte de formule dynamique qui extrait les valeurs correspondantes de différentes colonnes, en fonction de la colonne dans laquelle la formule est copiée :

• =SIERREUR(RECHERCHEV($A2, Est!$A$2:$C$6, COLONNES($A$1:B$1), FAUX), SIERREUR(RECHERCHEV($A2, Ouest!$A$2:$C$6, COLONNES( $A$1:B$1), FALSE), «Non trouvé»))

Lorsqu'il est inscrit dans la colonne B, COLONNES ($A$1:B$1) évalue à 2 et demande à RECHERCHEV de renvoyer une valeur de 2 deuxième colonne de la matrice du tableau.

Lorsque vous copiez dans la colonne C (c'est-à-dire que vous avez fait glisser la formule de B2 vers C2), B$1 devient C$1 car la référence de colonne est relative. Par conséquent, COLONNES ($A$1:C$1) est évalué à 3, ce qui oblige la fonction RECHERCHEV dans Excel à renvoyer une valeur de la troisième colonne.

Cette formule fonctionne très bien pour 2 ou 3 feuilles de recherche. Si vous en avez plus, les IFERROR répétitives deviennent trop lourdes. L'exemple suivant illustre une approche légèrement plus compliquée mais beaucoup plus élégante.

Méthode 5 : Rechercher plusieurs feuilles avec INDIRECT

Une autre façon de rechercher V entre plusieurs feuilles dans Excel consiste à utiliser une combinaison de fonctions RECHERCHEV et INDIRECT. Cette méthode nécessite un peu de préparation, mais au final, vous obtiendrez une formule plus compacte pour Vlookup sur un nombre illimité de feuilles de calcul.

Une formule générique pour Vlookup sur les feuilles est la suivante :

• RECHERCHEV ( valeur de recherche, INDIRECT («'» & INDEX ( Feuilles de recherche , MATCH (1, – (COUNTIF (INDIRECT («'» & Feuille_liste & «'! Plage_de recherche "), valeur de recherche )> 0), 0)) & «'! table_array "), col_index_num , FAUX)

Dónde:

• Feuilles de recherche- Une plage nommée composée des noms des feuilles de recherche.
• Valeur_de recherche: la valeur à rechercher.
• Plage_de recherche- La plage de colonnes dans les feuilles de recherche où rechercher la valeur de recherche.
• Tableau de tableaux- La plage de données dans les feuilles de recherche.
• Col_index_num- Le numéro de la colonne du tableau à partir de laquelle une valeur est renvoyée.

Pour que la formule fonctionne correctement, vous devez prendre en compte les avertissements suivants :

1. Il s'agit d'une formule matricielle qu'il faut compléter en appuyant sur Ctrl + Maj + Entrée clés ensemble.
2. Toutes les feuilles doivent avoir le même ordre des colonnes.
3. Puisque vous utilisez un tableau de tableaux pour toutes les feuilles de recherche, spécifiez le la plus grande gamme si vos feuilles ont un nombre de lignes différent.

Comment utiliser la formule pour Vlookup sur les feuilles

Pour utiliser la fonctionnalité Vlookup sur plusieurs feuilles à la fois, vous devez suivre ces étapes :

• étape 1: Écrivez tous les noms de feuilles de recherche quelque part dans votre classeur et nommez cette plage (Feuilles de recherche dans ce cas).

• étape 2: Ajustez la formule générique pour vos données. Dans cet exemple, ce serait :
  • en recherchant la valeur A2 (valeur de recherche)
  • dans la plage A2 : A6 (recherche_range) sur quatre feuilles de calcul (Est, Nord, Sud et Ouest) et
  • extraire les valeurs correspondantes de la colonne B, qui est la colonne 2 (сol_index_num) dans la plage de données A2: C6 (table_array).

Avec les arguments précédents, la formule prend cette forme :

• =RECHERCHEV ($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FAUX)

NOTE: Veuillez noter que nous bloquons les deux gammes ($A$2 :$A$6 et $A$2 :$C$6) avec des références de cellule absolues.

• étape 3: Saisissez la formule dans la cellule du haut (B2 dans cet exemple) et appuyez sur Ctrl + Maj + Entrée pour le compléter.
• étape 4: Double-cliquez ou faites glisser la poignée de remplissage pour copier la formule dans la colonne.

De ce fait, vous aurez la formule pour rechercher le numéro de commande sur 4 feuilles et récupérer l'article correspondant. Si un numéro de commande spécifique n'est pas trouvé, une erreur s'affiche #N / A comme à la ligne 14 :

Pour restituer le montant, remplacez simplement 2 par 3 dans l'argument col_index_num puisque les quantités sont dans la 3ème colonne du tableau matriciel :

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«'» & Lookup_sheets & «'!$A$2:$A$6»), $A2)>0) , 0)) & «'!$A$2:$C$6»), 3, FAUX)

Si vous souhaitez remplacer la notation d'erreur standard #N / A avec votre propre texte, enveloppez la formule dans la fonction IFNA :

• =IFNA (RECHERCHEV($A2, INDIRECT («'»&INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'» & Lookup_sheets & «'!$A$2:$A$6»), $A2)> 0), 0)) & «'!$A$2:$C$6»), 3, FALSE), «Non trouvé»)

Rechercher plusieurs feuilles entre les classeurs

Cette formule générique (ou toute variante) peut également être utilisée pour afficher plusieurs feuilles en une seule. classeur différent. Pour ce faire, concaténez le nom du classeur dans INDIRECT comme indiqué dans la formule suivante :

• =IFNA(RECHERCHEV ($A2, INDIRECT («'[Book1.xlsx]» & INDEX (Lookup_sheets, MATCH (1, –(COUNTIF(INDIRECT («'[Book1.xlsx]» & Lookup_sheets & «'!$A$2 :$A$6»), $A2)>0), 0)) & «'!$A$2:$C$6»), 2, FALSE), «Non trouvé»)

Vlookup entre les feuilles et renvoie plusieurs colonnes

Si vous souhaitez extraire des données de plusieurs colonnes, une formule de tableau multicellulaire vous pouvez le faire en une seule fois. Pour créer une telle formule, fournissez une constante de tableau pour l'argument col_index_num.

Dans cet exemple, nous renvoyons les noms des éléments (colonne B) et les quantités (colonne C), qui sont respectivement les 2e et 3e colonnes de la matrice du tableau. La matrice recherchée est donc 2,3 {}.

• =VLOOKUP($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(«'»& Lookup_sheets &»'!$A$2:$C$6″), $A2)>0) , 0)) &»'!$A$2:$C$6»), {2,3}, FAUX)

Pour réussir à saisir la formule dans plusieurs cellules, voici ce que vous devez faire :

• étape 1: Dans la première ligne, sélectionnez toutes les cellules à remplir (B2:C2 dans cet exemple).
• étape 2: Écrivez la formule et appuyez sur les touches Ctrl + Maj + Entrée. Cela entre la même formule dans les cellules sélectionnées, qui renverra une valeur différente dans chaque colonne.
• étape 3: faites glisser la formule vers les lignes restantes.

Comment fonctionne la formule de la fonction RECHERCHEV dans Excel

Pour mieux comprendre la logique, décomposons cette formule de base en fonctions individuelles :

• =RECHERCHEV ($A2, INDIRECT («'»&INDEX(Lookup_sheets, MATCH (1, –(COUNTIF (INDIRECT («'»& Lookup_sheets&»'!$A$2:$A$6″), $A2)>0), 0)) &»'!$A$2:$C$6»), 2, FAUX)

En travaillant de l’intérieur vers l’extérieur, voici ce que fait la formule :

COUNTIF et INDIRECT

Bref, jeNDIRECT crée les références pour toutes les feuilles de recherche et COUNTIF compte les occurrences de la valeur de recherche (A2) dans chaque feuille :

• –(COUNTIF(INDIRECT(«'»&Lookup_sheets&»'!$A$2:$A$6»), $A2)>0)

Plus en détail :

Tout d'abord, concaténez le nom de la plage (Lookup_sheets) et la référence de gamme ($2 $A : 6$A$), ajouter des apostrophes et le point d'exclamation aux bons endroits pour créer une référence externe et transmettre la chaîne de texte résultante à la fonction INDIRECT pour référencer dynamiquement les feuilles de recherche :

• INDIRECT ({«'Est'!$A$2:$A$6»; «'Sud'!$A$2:$A$6»; «'Nord'!$A$2:$A$6»; «'Ouest'! 2 $A : 6 $A$»})

DIRE.SI vérifie chaque cellule de la plage A2: A6 sur chaque feuille de recherche avec la valeur en A2 sur la feuille parent et renvoie le nombre de correspondances pour chaque feuille.

Dans cet ensemble de données, le numéro de commande dans A2 (101) est dans l' hoja Ouest, c'est 4 ^º dans la plage nommée, donc COUNT OUI renvoie ce tableau :

• {0 ; 0 ; 0 ; 1}

Ensuite, comparez chaque élément du tableau ci-dessus avec 0 :

• –({0 ; 0 ; 0 ; 1}>0)

Cela produit un tableau de valeurs VRAI (supérieur à 0) et FAUX (égal à 0), qui contraint 1 et 0 en utilisant un unaire double (-), et vous obtenez le tableau suivant comme résultat :

• {0 ; 0 ; 0 ; 1}

Cette opération constitue une précaution supplémentaire pour gérer une situation dans laquelle une feuille de recherche contient plusieurs occurrences de la valeur de recherche, dans laquelle Cas COUNTIF renverrait un nombre supérieur à 1, alors que vous voulez juste 1 et 0 dans la matrice finale (dans un instant, vous comprendrez pourquoi).

Après toutes ces transformations, la formule ressemble à ceci :

• RECHERCHEV($A2, INDIRECT(«'»&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &»'!$A$2:$C$6»), 2, FALSE)

INDEX et CORRESPONDANCE

À ce stade, une combinaison classique de CORRESPONDANCE D'INDICE va à :

• INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))

La fonction COINCIDIR défini sur une correspondance exacte (0 dans le dernier argument) recherche la valeur 1 dans le tableau {0 ; 0 ; 0 ; 1} et renvoie sa position, qui est 4 :

INDEX(Lookup_sheets, 4)

La fonction INDEX utiliser le numéro renvoyé par COINCIDIR comme argument de numéro de ligne (numéro_ligne) et renvoie la quatrième valeur de la plage nommée Feuilles de recherche, Qu'est que c'est Ouest.

La formule se réduit donc à :

• RECHERCHEV($A2, INDIRECT(«'»&»Ouest»&»'!$A$2:$C$6»), 2, FALSE)

RECHERCHEV et INDIRECT

La fonction INDIRECT traiter la chaîne de texte à l'intérieur :

• INDIRECT («'»&»Ouest»&»'!$A$2:$C$6»)

Et le transforme en une référence qui va à l'argumentation table_array à partir de RECHERCHEV :

• RECHERCHEV ($A2, 'Ouest'!$A$2 :$C$6, 2, FALSE)

Enfin, cette formule RECHERCHEV très standard recherche la valeur A2 dans la première colonne de la plage A2:C6 sur la feuille Ouest et renvoie une correspondance de la deuxième colonne.

RECHERCHEV dynamique pour renvoyer les données de plusieurs feuilles vers différentes cellules

Tout d’abord, définissons ce que signifie exactement le mot "dynamique" dans ce contexte et en quoi cette formule différera des précédentes.

Si vous disposez de grandes quantités de données dans le même format réparties sur plusieurs feuilles de calcul, vous souhaiterez peut-être extraire les informations de différentes feuilles dans différentes cellules. L'image suivante illustre le concept :

Contrairement aux formules précédentes qui récupéraient une valeur d'une feuille spécifique en fonction d'un identifiant unique, Cette fois vous chercherez à extraire les valeurs de plusieurs feuilles en même temps.

Il existe deux solutions différentes pour cette tâche. Dans les deux cas, vous devez effectuer un petit travail de préparation et créer des plages nommées pour les cellules de données dans chaque feuille de recherche. Pour cet exemple, nous définissons les plages suivantes :

• East_Sales- A2 : B6 sur la feuille Est
• North_Sales- A2 : B6 sur la feuille Nord
• South_Sales- A2 : B6 sur la feuille Sud
• West_Sales- A2 : B6 sur la feuille Ouest

RECHERCHEV et IF imbriqués

Si vous disposez d'un nombre raisonnable de feuilles à rechercher, vous pouvez utiliser fonctions IF imbriquées pour sélectionner la feuille en fonction des mots-clés dans les cellules prédéfinies (cellules B1 à D1 dans ce cas).

Avec la valeur de recherche dans A2, la formule est la suivante :

• =RECHERCHEV ($A2, IF (B$1=»est», East_Sales, IF (B$1=»nord», North_Sales, IF (B$1=»sud», South_Sales, IF (B$1=»ouest», West_Sales) ))), 2, FAUX)

Traduit en anglais, la partie IF dit:

Si B1 est Est, recherche la plage nommée Est_Ventes; si B1 est Nord, recherche la plage appelée Nord_Ventes; si B1 est Région Sud, recherche la plage appelée Sud_Ventes; et si B1 est Ouest, recherche la plage appelée Ouest_Ventes.

La plage renvoyée par IF va table_array de RECHERCHEV, qui extrait une valeur correspondante de la deuxième colonne de la feuille correspondante.

L'utilisation intelligente de références mixtes pour la valeur de recherche ($A2 – colonne absolue et ligne relative) et le test logique IF (B$1 – colonne relative et ligne absolue) permet de copier la formule dans d'autres cellules sans aucune modification – Excel ajuste automatiquement les références en fonction de la position relative d'une ligne et d'une colonne.

Ainsi, vous saisissez la formule dans B2, la copiez vers la droite et vers le bas dans autant de colonnes et de lignes que nécessaire, et vous obtiendrez le résultat suivant :

RECHERCHE V INDIRECTE

Lorsque vous travaillez avec de nombreuses feuilles, plusieurs niveaux imbriqués peuvent rendre la formule trop longue et difficile à lire. Une bien meilleure façon est de créer un plage de recherche dynamique avec l'aide d'INDIRECT :

• =RECHERCHEV ($A2, INDIRECT (B$1&»_Sales»), 2, FALSE)

Ici, nous concaténons la référence à la cellule contenant une partie unique de la plage nommée (B1) et la partie commune (_Ventes). Cela produit une chaîne de texte comme «Ventes_Est», que INDIRECT convertit en nom de plage lisible par Excel.

Le résultat est une formule compacte qui fonctionne à merveille sur n'importe quel nombre de feuilles :

Vous pourriez être intéressé à en savoir plus sur : Comment regrouper un tableau croisé dynamique par mois dans Excel

Comme vous le verrez, voici les façons d'utiliser le Fonction RECHERCHEV dans Excel ainsi que d'autres fonctions de recherche par feuille telles que Vlookup qui peuvent beaucoup vous aider à trouver le données ce que vous voulez réaliser entre feuilles et fichiers dans Excel. Nous espérons vous avoir aidé.