Comment utiliser une formule RECHERCHEV pour obtenir des données à partir dune liste Excel. Résoudre les problèmes de RECHERCHEV lorsque les choses tournent mal. Vidéos étape par étape et classeurs Excel avec des exemples RECHERCHEV.
Vidéo RECHERCHEV
Regardez les étapes de création dune formule RECHERCHEV dans la vidéo de recherche de prix du produit, illustrée ci-dessous. Les instructions écrites se trouvent sous la vidéo. Obtenez le fichier dexemple de recherche du prix du produit à suivre avec la vidéo (fichier à télécharger n ° 4).
Sélectionnez un emplacement pour une table de recherche
Cest une bonne idée de stocker chaque recherche table sur une feuille de calcul distincte du classeur. Ensuite, lorsque vous ajoutez et supprimez des lignes dans les tables de recherche, vous najouterez ni ne supprimerez accidentellement des lignes dans aucune autre table.
Dans cet exemple, la table de recherche se trouve sur une feuille nommée Produits.
Créer une table de recherche
Les formules de recherche peuvent fonctionner verticalement, rechercher des valeurs dans une colonne, ou elles peuvent fonctionner horizontalement, en recherchant des valeurs sur une ligne . Dans cet exemple, les informations seront stockées verticalement, avec des valeurs dans une colonne, et plus tard, nous utiliserons une formule VLookup pour effectuer une recherche verticale.
- Saisissez les en-têtes dans la première ligne
- La première colonne doit contenir les valeurs de clé uniques sur lesquelles vous allez baser la recherche. Dans cet exemple, vous pouvez trouver le prix dun code produit spécifique.
- Si vous avez dautres données sur la feuille de calcul, laissez au moins une ligne vide en bas du tableau et une colonne vide à droite du tableau, pour séparer les table de recherche à partir des autres données.
Remarque: Pour faciliter la référence au tableau, vous pouvez nommer la plage. Il y a des instructions ici: Nommer une plage ▲ TOP
Arguments de la fonction VLOOKUP
La fonction VLOOKUP a quatre arguments:
- valeur_recherche : Quelle valeur voulez-vous rechercher? Dans cet exemple, le code produit se trouve dans la cellule A7 et vous voulez trouver son pr nom du produit.
- table_array: Où se trouve la table de recherche? Si vous utilisez une référence absolue ($ A $ 2: $ C $ 5), au lieu dune référence relative (A2: C5), il sera plus facile de copier la formule dans dautres cellules. Ou, nommez la table de recherche et faites-y référence par son nom.
- col_index_num: Quelle colonne a la valeur que vous voulez renvoyer? Dans cet exemple, les noms de produits se trouvent dans la deuxième colonne de la table de recherche.
- : Voulez-vous une correspondance exacte? Une correspondance approximative est-elle acceptable?
Si vous utilisez TRUE comme dernier argument, ou omettez le dernier argument, une correspondance approximative peut être renvoyée. Cet exemple a FALSE comme dernier argument, donc si le code produit nest pas trouvé, le résultat sera # N / A. (Remarque: Excel est plutôt indulgent et acceptera 0 au lieu de FALSE, et 1 au lieu de TRUE.) ▲ TOP
Créer une formule VLookup
Une fois que vous avez créé la table de recherche, vous pouvez créer dautres formules dans le classeur et extraire des informations de la liste de produits. Par exemple, pour préparer une facture, vous pouvez entrer un code produit et les formules obtiendront le nom du produit ou le prix de la table des produits.
Dans cet exemple, la facture est créée sur une feuille nommée Facture. La formule RECHERCHEV devrait trouver une correspondance exacte pour le code produit et renvoyer le nom du produit.
Pour créer la formule RECHERCHEV qui calcule le prix du produit, procédez comme suit:
- Sélectionnez la feuille de facturation
- Entrez le code produit A23 dans la cellule A7
- Dans la cellule B7, lancez la formule RECHERCHEV:
= RECHERCHEV ( - Cliquez sur cellule A7 pour entrer sa référence.
- Tapez une virgule, pour terminer le premier argument
- Sélectionnez la fiche Produits
- Sélectionnez les cellules A2: C5 (la liste de produits )
- Appuyez sur la touche F4 pour changer les références de cellule en absolues. La formule doit maintenant ressembler à ceci:
= RECHERCHEV (A7, Produits! $ A $ 2: $ C $ 5 - Tapez une virgule pour terminer le deuxième argument.
- Tapez un 2, qui est la colonne de la table de recherche contenant le nom du produit.
- Tapez une virgule pour terminer le troisième argument.
- Tapez FALSE, pour spécifier quune correspondance exacte pour le code produit est trouvée, et ajoutez le crochet fermant.
- La formule doit maintenant ressembler à l Comme ceci:
= RECHERCHEV (A7, Produits! $ A $ 2: $ C $ 5,2, FALSE) - Appuyez sur la touche Entrée pour terminer la formule. Le nom du produit sera affiché.
Remarque: pour renvoyer le prix du produit, créez une formule RECHERCHEV qui fait référence à la colonne 3 de la table de recherche. Par exemple, entrez la formule suivante dans la cellule C7:
= RECHERCHEV (A7, Produits! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOP
Formule RECHERCHEV pour la plage de valeurs
Dans certaines situations, une correspondance approximative est préférée, donc plusieurs valeurs renverront le même résultat. Par exemple, lors de la notation des papiers des élèves, tous les papiers avec une note de 85 ou plus devraient recevoir une note A.
Pour voir les étapes de création de cette formule, veuillez regarder la vidéo RECHERCHEV ci-dessous. Les instructions écrites se trouvent sous la vidéo.
Dans cet exemple, la table de recherche est créée sur une feuille nommée Notes. Pour créer la table de recherche, entrez le score minimum pour chaque note dans la colonne A. Saisissez la note correspondante dans la colonne B. Triez les notes par ordre croissant.
Les cellules A2: B6 ont été nommées GradeList.
Les scores sont saisis sur une feuille nommée Report Card, où une formule RECHERCHEV calcule la note.
Dans le capture décran ci-dessous, la formule a été copiée à la ligne 6, et vous pouvez voir la formule dans la cellule C6.
Combiner RECHERCHEV avec MATCH
Au lieu de taper le numéro de colonne dans une formule RECHERCHEV, utilisez la fonction MATCH pour trouver la colonne correcte dans la table de recherche. Ceci présente quelques avantages:
- Rend la formule flexible, ce qui facilite la copie de la formule dans une feuille de calcul.
- Peut éviter les problèmes si de nouvelles colonnes sont ajoutées dans la table de recherche, ou si les colonnes de recherche sont réorganisées.
Cette vidéo montre les étapes, et il y a des instructions écrites pour un autre exemple, sous la vidéo.
Votre navigateur ne peut pas afficher ce cadre. Voici un lien vers la page
RECHERCHEV avec MATCH pour les détails de la commande
Dans cet exemple, une formule RECHERCHEV renvoie les détails de la commande à partir dune table de recherche, en fonction du numéro didentification de la commande . Voici la table de recherche, nommée tblOrders.
REMARQUE: Cet exemple se trouve dans lexemple de classeur n ° 1, sur la feuille nommée OrdersMATCH.
Voici la feuille de calcul avec les formules RECHERCHEV. Nous voulons la région, la date de la commande et le montant de la commande pour chaque commande, donc 3 formules VLOOKUP sont nécessaires.
Si les numéros de colonne sont saisis dans la formule, une formule différente est nécessaire dans chaque colonne:
- Region: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
La fonction MATCH
Au lieu de taper le numéro de colonne dans la formule RECHERCHEV, nous pouvons utiliser la fonction MATCH. La fonction MATCH trouve la position dun élément dans une liste et renvoie le numéro de position.
Dans la capture décran ci-dessous, la formule MATCH renvoie 2 comme position de « Region », dans les cellules den-tête (A1: D1) de la table de recherche.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
REMARQUE: Pour que cette technique fonctionne correctement, le les en-têtes de la feuille RECHERCHEV doivent correspondre exactement aux en-têtes de la table de recherche. Pour garantir une correspondance exacte, les cellules den-tête RECHERCHEV sont liées aux cellules den-tête de la table de recherche.
Ajouter MATCH à RECHERCHEV
Pour ajouter la fonction MATCH à la formule RECHERCHEV, remplacez simplement le numéro de colonne saisi
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
avec la formule MATCH:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Copiez la formule RECHERCHEV dans
Maintenant, au lieu davoir besoin dune formule différente dans chaque colonne, vous pouvez copier la formule à travers, et utiliser la même formule dans toutes les colonnes. Dans chaque colonne, il fera référence à la cellule den-tête de cette colonne et trouvera sa position dans la table de recherche.
REMARQUE: Si vous remplissez la formule sur des colonnes avec un formatage différent, procédez comme suit:
- Sélectionnez la cellule contenant la formule que vous souhaitez copier
- Pointez sur la poignée de remplissage de la cellule sélectionnée (petit carré dans le coin inférieur droit)
- Appuyez sur le bouton DROIT de la souris et faites glisser jusquà la dernière cellule qui a besoin de la formule
- Relâchez le bouton de la souris et cliquez sur Remplir sans mise en forme
Combiner IF et VLOOKUP
Vous pouvez utiliser une formule IF avec une formule VLookup, pour renvoyer des valeurs exactes si elles sont trouvées, et une chaîne vide sinon trouvé.
Pour voir les étapes de configuration de la formule IF et VLOOKUP, vous pouvez regarder cette courte vidéo. Les instructions écrites se trouvent sous la vidéo.
Pour masquer les erreurs en combinant IF avec RECHERCHEV, procédez comme suit:
- Sur la feuille de facture, dans la cellule A8, saisissez le produit code A28. Si la formule VLookup dans la cellule B8 a FALSE comme quatrième argument, le résultat est # N / A, car il ny a pas de correspondance exacte pour le code produit dans la table de recherche.
- Enveloppez la formule VLookup avec un Formule IF (dans cet exemple, la liste de produits a été nommée), en utilisant la fonction ISNA pour rechercher une erreur # N / A:
- Appuyez sur la touche Entrée et la cellule apparaît vide. Étant donné quaucune correspondance exacte na été trouvée, la formule VLookup a renvoyé un # N / A, de sorte que le résultat de la fonction ISNA est TRUE. La formule IF la converti en une chaîne vide.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), « », RECHERCHEV (A8, ProductList, 2, FALSE))
Combinez IFERROR et VLOOKUP
Merci à Chip Pearson davoir suggéré cette formule.
Dans Excel 2007, une nouvelle fonction, IFERROR, a été introduite . Vous pouvez utiliser une formule IFERROR avec VLookup pour rechercher une valeur dans plusieurs tables et renvoyer les informations associées lorsquelles sont trouvées. Dans cet exemple, trois régions, Ouest, Est et Centre, ont des feuilles de commande. Sur chaque feuille se trouve une plage nommée – OrdersW, OrdersE et OrdersC.
Sur une feuille nommée Commandes, vous pouvez saisir un ID de commande, puis utiliser une RECHERCHEV avec IFERROR pour vérifier chaque plage nommée et affichez les informations sur la commande sélectionnée.
- Sur la feuille de commande, dans la cellule B6, entrez un 4 comme OrderID. Cette commande a été passée dans la région Centre.
- Pour simplement vérifier la table de la région Est, la formule RECHERCHEV dans la cellule C6 serait:
= RECHERCHEV (B6, CommandesE, 2, FALSE) - Appuyez sur la touche Entrée et la formule RECHERCHEV renvoie un # N / A, car lID de commande 4 ne se trouve pas dans la table de commande des régions de lEst.
- Parce quune commande pourrait avoir a été placé dans lune des trois régions, vous avez besoin dune formule qui vérifiera chaque tableau.
Si lID de commande ne se trouve pas dans le premier tableau, la formule doit vérifier le deuxième tableau. Si lID de commande ne se trouve pas dans le second table, il doit vérifier le troisième tableau. Si lID de commande nest pas dans le troisième tableau, un message « Not Found » doit apparaître dans la cellule. - La formule IFERROR vous permet de vérifier une valeur, puis spécifiez ce quil faut faire si une erreur est détectée. Si vous utilisez IFERROR avec la formule existante, vous pouvez afficher « Not Found », au lieu de lerreur # N / A:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), « Not Found ») - Pour vérifier les trois tables, vous pouvez ensuite les formules IFERROR et VLOOKUP:
Ceci vérifie la table OrdersE et si une erreur est trouvée, vérifie la table OrdersW, puis OrdersC. Si le OrderID nest trouvé dans aucune des trois tables, le message Not Found saffiche dans la cellule.
Vous pouvez également vérifier plusieurs tables dans les anciennes versions dExcel, où IFERROR nest pas disponible, à laide dun formule plus longue:
RECHERCHEV pour les valeurs combinées
Dans certaines tables, il peut ne pas y avoir de valeurs uniques nimporte quelle colonne de la table de recherche. Par exemple, dans le tableau ci-dessous, Veste est répertoriée deux fois dans la colonne A. Cependant, il ny a quun seul enregistrement pour chaque combinaison de veste et de taille – Veste moyenne à la ligne 4 et Veste grande à la ligne 5.
Si vous avez besoin de trouver le prix dune grande veste, une RECHERCHEV basée uniquement sur la colonne A renverrait le prix de la première veste répertoriée (Medium ). Vous sous-évalueriez le prix de la veste – en la vendant à 60,00 au lieu de 65,00.
Pour créer des valeurs de recherche uniques, vous peut insérer une nouvelle colonne sur le côté gauche du tableau et utiliser une formule pour combiner le produit et la taille. Dans la cellule A2, la formule combine la valeur en B2 et le caractère pipe et la valeur en C2.
= B2 & « | » & C2
Copiez cette formule jusquà la dernière ligne de données, afin que chaque ligne ait une valeur unique dans la colonne A.
Remarque : Au lieu du caractère pipe, vous pouvez utiliser un autre caractère qui nest pas inclus dans vos données.
Ensuite, dans une formule RECHERCHEV, combinez le produit et la taille en tant que valeur_Recherche. Dans la cellule H1, la formule combine la valeur en F1 et le caractère pipe et la valeur en G1.
= RECHERCHEV (F1 & » | « & G1, $ A $ 2: $ D $ 5,4, FALSE)
Remarque: Le prix est maintenant colonne 4, au lieu de la colonne 3. ▲ TOP
Dépanner la formule RECHERCHEV
Votre formule RECHERCHEV peut renvoyer un # N / A, même si la valeur que vous recherchez semble se trouver dans la table de recherche. Les causes courantes de ceci sont:
Texte contre nombre
Caractères despacement
Autres caractères
Texte contre nombre
Une cause fréquente de cette erreur est que lune des valeurs est un nombre et lautre est du texte. Par exemple, la table de recherche peut contenir « 123 (texte) et la valeur à rechercher est 123 (un nombre).
- Si possible, convertissez le texte en nombres, en utilisant lun des méthodes présentées ici: Convertir du texte en nombres
- Si vous ne pouvez pas convertir les données, vous pouvez convertir la valeur de recherche dans la formule RECHERCHEV, en utilisant lune des solutions ci-dessous:
- Valeurs de recherche sont du texte, le tableau a des nombres
- Les valeurs de recherche sont des nombres, le tableau a du texte
- Pour déterminer quelles valeurs sont du texte et lesquelles sont des nombres, voir le numéro détaillé ou les étapes de dépannage de texte
1. Les valeurs de recherche sont Text et la table contient des nombres
Si la table de recherche contient des nombres et que la valeur à rechercher est du texte, utilisez une formule similaire à la suivante:
= RECHERCHEV (–A7, Produits!$ A $ 2: $ C $ 5,3, FALSE)
Le double unaire (-) convertit le texte en nombre et fonctionnera correctement même si les valeurs de recherche sont des nombres.
2. Les valeurs de recherche sont des nombres et la table contient du texte
Si la table de recherche contient du texte et que la valeur à rechercher est numérique, utilisez une formule similaire à la suivante:
= RECHERCHEV (A7 & « »), Produits! $ A $ 2: $ C $ 5,3, FALSE)
OR
= RECHERCHEV (TEXTE (A7, « 00000 ») , Produits! $ A $ 2: $ C $ 5,3, FALSE)
La fonction TEXT convertit un nombre en texte et fonctionnera correctement même si les valeurs de recherche sont du texte. Dans le premier exemple, lopérateur & crée une chaîne de texte à partir dun nombre non formaté. Dans le deuxième exemple, un nombre formaté avec des zéros non significatifs (par exemple 00123) correspondrait à un texte « nombre » avec des zéros non significatifs.
Pour voir les étapes de résolution du problème RECHERCHEV lorsque la table de recherche contient des valeurs de texte, regardez ce court didacticiel vidéo.
Espaces dans une valeur, et pas dans lautre
Une autre cause potentielle de labsence de valeur correspondante est une différence despaces. Lune des valeurs peut contenir des espaces de début (ou de fin, ou des espaces incorporés), et lautre ne « t ». Pour tester les valeurs, vous pouvez utiliser la fonction LEN, pour vérifier la longueur de chaque valeur.
Par exemple: = LEN (A7) renverra le nombre de caractères dans la cellule A7. Il doit être égal au nombre de caractères dans la cellule correspondante dans la table de recherche.
Si possible, supprimez les éléments inutiles espaces et la formule RECHERCHEV devrait fonctionner correctement. Si vous ne pouvez pas supprimer les espaces, utilisez la fonction TRIM dans la RECHERCHEV, pour supprimer les espaces de début, de fin ou en double. Par exemple:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Autres caractères
Si la fonction TRIM seule ne résout pas le problème , vous pouvez essayer lune des suggestions suivantes:
- Fonction SUBSTITUTE
- Fonction CLEAN
- Macro pour supprimer les caractères Web
Fonction SUBSTITUTE
Utilisez la fonction SUBSTITUTE pour supprimer les caractères indésirables. Il existe un exemple sur le blog Contextures: Nettoyer les données Excel avec TRIM et SUBSTITUTE
Fonction CLEAN
Une autre façon de résoudre les problèmes de RECHERCHEV est avec la fonction CLEAN, qui peut supprimer certains caractères indésirables du texte. Il y a plus dinformations sur la fonction CLEAN dans cet article de blog Contextures: 30 fonctions Excel en 30 jours: 29 – CLEAN
Caractères Web dans une valeur, et pas dans lautre
Si vous avez copié des données à partir dune page Web, elle peut contenir un espace HTML insécable (& nbsp) caractères. David McRitchie a écrit une macro pour les supprimer, avec o autres caractères despaces
Problèmes lors du tri de la formule RECHERCHEV
Une formule RECHERCHEV peut dabord renvoyer les résultats corrects, mais affiche ensuite des résultats incorrects si la liste des éléments est triée. Cela peut se produire si la référence à la valeur de recherche inclut un nom de feuille. Par exemple:
= RECHERCHEV (« Bon de commande »! B5, Produits! $ B $ 2: $ C $ 6,2, FALSE)
REMARQUE: Ce problème peut survenir avec dautres fonctions aussi, comme une formule de recherche INDEX / MATCH.
Regardez cette vidéo pour voir les étapes de résolution du problème, et téléchargez le fichier dexemple de problème de tri RECHERCHEV à suivre. Les instructions écrites se trouvent sous la vidéo.
Noms des feuilles dans la référence
Ce type de référence est créé si vous cliquez sur une autre feuille lors de la construction de la formule. Dès que vous faites cela, Excel ajoute le nom de la feuille à toutes les références suivantes dans la formule.
Dans la capture décran ci-dessus, la robe est dans la cellule B9 et la cellule C9 montre le prix correct de 30 $.
Cependant, après avoir trié les produits de A à Z, la robe se déplace jusquà la cellule B5, mais la formule de la cellule C5 continue de faire référence à la cellule B9. En raison des noms de feuille dans les références, Excel conserve les références dorigine au lieu de conserver une référence à la ligne actuelle. La cellule C5 montre le prix dun pull au lieu dune robe. ▲ TOP
Résoudre le problème
Pour résoudre le problème, supprimez tous les noms de feuille inutiles du Références de cellule RECHERCHEV. Voici la formule révisée pour la cellule C5:
= RECHERCHEV (B5, Produits! $ B $ 2: $ C $ 6,2, FALSE)
Une fois les noms de feuille inutiles supprimés, la liste peut être triée en toute sécurité et les résultats corrects safficheront pour chaque élément.
Téléchargez les fichiers dexemple
1. Obtenez lexemple de classeur RECHERCHEV (Excel 2007 et versions ultérieures). Le fichier zippé est au format xlsx et ne contient aucune macros.
2. Pour Excel 2003, obtenez cette version de lexemple de fichier RECHERCHEV. Le fichier zippé est au format xls et ne contient aucune macros.
3. Pour voir le problème qui peut se produire lors du tri avec RECHERCHEV, obtenez cet exemple de fichier de problème de tri RECHERCHEV. Le fichier zippé est au format xlsx et ne contient aucune macros.
4. Obtenez le classeur de recherche de prix de produit, utilisé dans le didacticiel vidéo.Le fichier compressé est au format xlsx et ne contient aucune macros. ▲ TOP
Plus de didacticiels
Dépannage du numéro / texte de RECHERCHEV
RECHERCHEV à partir dun autre classeur
RECHERCHEH
CHOISIR Fonction
Recherche – 2 critères
RECHERCHE
INDEX / MATCH
Comparer les fonctions de recherche
Liste des fonctions