En combinant la fonction RECHERCHEV d'Excel avec la fonction COLONNE, vous pouvez créer une formule de recherche qui renvoie plusieurs valeurs à partir d'une seule ligne d'une base de données ou d'un tableau de données. Découvrez comment créer une formule de recherche qui renvoie plusieurs valeurs à partir d'un seul enregistrement de données.
Les instructions de cet article s'appliquent à Excel 2019, 2016, 2013, 2010; et Excel pour Microsoft 365.
Bottom Line
La formule de recherche nécessite que la fonction COLUMN soit imbriquée dans VLOOKUP. L'imbrication d'une fonction implique la saisie de la deuxième fonction comme l'un des arguments de la première fonction.
Entrez les données du didacticiel
Dans ce didacticiel, la fonction COLONNE est entrée comme argument de numéro d'index de colonne pour VLOOKUP. La dernière étape du didacticiel consiste à copier la formule de recherche dans des colonnes supplémentaires pour récupérer des valeurs supplémentaires pour la partie choisie.
La première étape de ce didacticiel consiste à saisir les données dans une feuille de calcul Excel. Afin de suivre les étapes de ce tutoriel, entrez les données affichées dans l'image ci-dessous dans les cellules suivantes:
- Entrez la plage supérieure de données dans les cellules D1 à G1.
- Entrez la deuxième plage dans les cellules D4 à G10.
Les critères de recherche et la formule de recherche créés dans ce didacticiel sont entrés dans la ligne 2 de la feuille de calcul.
Ce didacticiel n'inclut pas le formatage Excel de base illustré dans l'image, mais cela n'affecte pas le fonctionnement de la formule de recherche.
Créer une plage nommée pour la table de données
Une plage nommée est un moyen simple de faire référence à une plage de données dans une formule. Plutôt que de taper les références de cellule pour les données, tapez le nom de la plage.
Un deuxième avantage de l'utilisation d'une plage nommée est que les références de cellule de cette plage ne changent jamais, même lorsque la formule est copiée dans d'autres cellules de la feuille de calcul. Les noms de plage sont une alternative à l'utilisation de références de cellules absolues pour éviter les erreurs lors de la copie de formules.
Le nom de la plage n'inclut pas les en-têtes ou les noms de champs pour les données (comme indiqué à la ligne 4), uniquement les données.
-
Mettez en surbrillance cellules D5 à G10 dans la feuille de calcul.
Image -
Placez le curseur dans la zone Nom située au-dessus de la colonne A, tapez Table, puis appuyez sur Entrée. Les cellules D5 à G10 portent le nom de plage Table.
Image - Le nom de la plage pour l'argument de tableau de la table RECHERCHEV est utilisé plus loin dans ce didacticiel.
Ouvrir la boîte de dialogue RECHERCHEV
Bien qu'il soit possible de taper la formule de recherche directement dans une cellule d'une feuille de calcul, de nombreuses personnes ont du mal à garder la syntaxe droite, en particulier pour une formule complexe telle que celle utilisée dans ce didacticiel.
Comme alternative, utilisez la boîte de dialogue Arguments de la fonction RECHERCHEV. Presque toutes les fonctions d'Excel ont une boîte de dialogue où chacun des arguments de la fonction est entré sur une ligne distincte.
-
Sélectionnez cellule E2 de la feuille de calcul. Il s'agit de l'emplacement où les résultats de la formule de recherche bidimensionnelle s'afficheront.
Image -
Sur le ruban, accédez à l'onglet Formules et sélectionnez Lookup & Reference.
Image -
Sélectionnez VLOOKUP pour ouvrir la boîte de dialogue Function Arguments.
Image - La boîte de dialogue Arguments de la fonction est l'endroit où les paramètres de la fonction RECHERCHEV sont entrés.
Entrez l'argument de valeur de recherche
Normalement, la valeur de recherche correspond à un champ de données dans la première colonne de la table de données. Dans cet exemple, la valeur de recherche fait référence au nom de la pièce pour laquelle vous souhaitez rechercher des informations. Les types de données autorisés pour la valeur de recherche sont les données textuelles, les valeurs logiques, les nombres et les références de cellule.
Références de cellules absolues
Lorsque des formules sont copiées dans Excel, les références de cellule changent pour refléter le nouvel emplacement. Si cela se produit, D2, la référence de cellule pour la valeur de recherche, change et crée des erreurs dans les cellules F2 et G2.
Les références de cellules absolues ne changent pas lorsque les formules sont copiées.
Pour éviter les erreurs, convertissez la référence de cellule D2 en une référence de cellule absolue. Pour créer une référence de cellule absolue, appuyez sur la touche F4. Cela ajoute des signes dollar autour de la référence de cellule, tels que $D$2.
-
Dans la boîte de dialogue Arguments de la fonction, placez le curseur dans la zone de texte lookup_value. Ensuite, dans la feuille de calcul, sélectionnez cell D2 pour ajouter cette référence de cellule à lookup_value. La cellule D2 est l'endroit où le nom de la pièce sera saisi.
Image -
Sans déplacer le point d'insertion, appuyez sur la touche F4 pour convertir D2 en référence de cellule absolue $D$2.
Image - Laissez la boîte de dialogue de la fonction RECHERCHEV ouverte pour la prochaine étape du didacticiel.
Entrez l'argument du tableau de table
Un tableau de tableau est le tableau de données que la formule de recherche recherche pour trouver les informations souhaitées. Le tableau de table doit contenir au moins deux colonnes de données.
La première colonne contient l'argument de valeur de recherche (qui a été configuré dans la section précédente), tandis que la deuxième colonne est recherchée par la formule de recherche pour trouver les informations que vous spécifiez.
L'argument du tableau de tableau doit être saisi soit comme une plage contenant les références de cellules pour la table de données, soit comme un nom de plage.
Pour ajouter la table de données à la fonction VLOOKUP, placez le curseur dans la zone de texte table_array dans la boîte de dialogue et tapez Tablepour entrer le nom de la plage pour cet argument.
Imbriquer la fonction COLONNE
Normalement, RECHERCHEV ne renvoie que les données d'une colonne d'une table de données. Cette colonne est définie par l'argument du numéro d'index de la colonne. Dans cet exemple, cependant, il y a trois colonnes et le numéro d'index de la colonne doit être modifié sans modifier la formule de recherche. Pour ce faire, imbriquez la fonction COLUMN dans la fonction VLOOKUP en tant qu'argument Col_index_num.
Lors de l'imbrication de fonctions, Excel n'ouvre pas la boîte de dialogue de la deuxième fonction pour entrer ses arguments. La fonction COLONNE doit être saisie manuellement. La fonction COLONNE n'a qu'un seul argument, l'argument Référence, qui est une référence de cellule.
La fonction COLONNE renvoie le numéro de la colonne fournie comme argument de référence. Il convertit la lettre de la colonne en un nombre.
Pour trouver le prix d'un article, utilisez les données de la colonne 2 du tableau de données. Cet exemple utilise la colonne B comme référence pour insérer 2 dans l'argument Col_index_num.
-
Dans la boîte de dialogue Function Arguments, placez le curseur dans la zone de texte Col_index_num et tapez COLUMN(. (Assurez-vous d'inclure le crochet ouvrant.)
Image -
Dans la feuille de calcul, sélectionnez cellule B1 pour saisir cette référence de cellule comme argument de référence.
Image - Tapez un crochet fermant pour terminer la fonction COLONNE.
Entrez l'argument de recherche de plage RECHERCHEV
L'argument Range_lookup de VLOOKUP est une valeur logique (TRUE ou FALSE) qui indique si VLOOKUP doit trouver une correspondance exacte ou approximative avec Lookup_value.
- TRUE ou Omitted: VLOOKUP renvoie une correspondance proche de Lookup_value. Si aucune correspondance exacte n'est trouvée, RECHERCHEV renvoie la valeur la plus grande suivante. Les données de la première colonne de Table_array doivent être triées par ordre croissant.
- FALSE: VLOOKUP utilise une correspondance exacte avec Lookup_value. S'il y a deux valeurs ou plus dans la première colonne de Table_array qui correspondent à la valeur de recherche, la première valeur trouvée est utilisée. Si aucune correspondance exacte n'est trouvée, une erreur N/A est renvoyée.
Dans ce didacticiel, des informations spécifiques sur un élément matériel particulier seront recherchées, de sorte que Range_lookup est défini sur FALSE.
Dans la boîte de dialogue Arguments de la fonction, placez le curseur dans la zone de texte Range_lookup et tapez False pour indiquer à VLOOKUP de renvoyer une correspondance exacte pour les données.
Sélectionnez OK pour terminer la formule de recherche et fermer la boîte de dialogue. La cellule E2 contiendra une erreur N/A car les critères de recherche n'ont pas été saisis dans la cellule D2. Cette erreur est temporaire. Il sera corrigé lorsque les critères de recherche seront ajoutés à la dernière étape de ce didacticiel.
Copiez la formule de recherche et entrez les critères
La formule de recherche récupère les données de plusieurs colonnes de la table de données à la fois. Pour ce faire, la formule de recherche doit résider dans tous les champs à partir desquels vous souhaitez obtenir des informations.
Pour récupérer les données des colonnes 2, 3 et 4 de la table de données (le prix, le numéro de pièce et le nom du fournisseur), entrez un nom partiel comme Lookup_value.
Étant donné que les données sont présentées de manière régulière dans la feuille de calcul, copiez la formule de recherche dans cellule E2 dans cellules F2 et G2 Lorsque la formule est copiée, Excel met à jour la référence de cellule relative dans la fonction COLONNE (cellule B1) pour refléter le nouvel emplacement de la formule. Excel ne modifie pas la référence de cellule absolue (telle que $D$2) et la plage nommée (Table) lorsque la formule est copiée.
Il existe plusieurs façons de copier des données dans Excel, mais la plus simple consiste à utiliser la poignée de remplissage.
-
Sélectionnez cell E2, où se trouve la formule de recherche, pour en faire la cellule active.
Image -
Faites glisser la poignée de remplissage vers cell G2. Les cellules F2 et G2 affichent l'erreur N/A présente dans la cellule E2.
Image -
Pour utiliser les formules de recherche pour récupérer des informations à partir de la table de données, dans la feuille de calcul, sélectionnez cellule D2, tapez Widget et appuyez sur Entrée.
Image Les informations suivantes s'affichent dans les cellules E2 à G2.
- E2: $14.76 - le prix d'un widget
- F2: PN-98769 - le numéro de référence d'un widget
- G2: Widgets Inc. - le nom du fournisseur de widgets
-
Pour tester la formule matricielle VLOOKUP, tapez le nom des autres parties dans la cellule D2 et observez les résultats dans les cellules E2 à G2.
Image - Chaque cellule contenant la formule de recherche contient une donnée différente sur l'élément matériel que vous avez recherché.
La fonction VLOOKUP avec des fonctions imbriquées comme COLUMN fournit une méthode puissante pour rechercher des données dans une table, en utilisant d'autres données comme référence de recherche.
