Exceller

23 choses à savoir sur RECHERCHEV

23 Things You Should Know About Vlookup

Lorsque vous souhaitez extraire des informations d'une table, la fonction RECHERCHEV Excel est une excellente solution. La possibilité de rechercher et de récupérer dynamiquement des informations à partir d'une table change la donne pour de nombreux utilisateurs, et vous trouverez RECHERCHEV partout.

Et pourtant, bien que RECHERCHEV soit un outil relativement facile à utiliser, il y en a beaucoup qui peuvent mal tourner. L'une des raisons est que RECHERCHEV a un défaut de conception majeur - par défaut, cela suppose que vous êtes d'accord avec une correspondance approximative. Ce que vous n'êtes probablement pas.



Cela peut entraîner des résultats semble tout à fait normal , même s'ils sont totalement incorrect . Croyez-moi, ce n'est PAS quelque chose que vous voulez essayer d'expliquer à votre patron, après qu'elle a déjà envoyé votre feuille de calcul à la direction :)



Lisez ci-dessous pour savoir comment gérer ce défi et découvrez d'autres conseils pour maîtriser le Fonction Excel VLOOKUP .

1. Comment fonctionne VLOOKUP

VLOOKUP est une fonction pour rechercher et récupérer des données dans une table. Le «V» dans RECHERCHEV signifie vertical, ce qui signifie que les données du tableau doivent être disposées verticalement, avec les données en lignes. (Pour les données structurées horizontalement, voir RECHERCHEH ).



Si vous avez un tableau bien structuré, avec des informations disposées verticalement et une colonne sur la gauche que vous pouvez utiliser pour faire correspondre une ligne, vous pouvez probablement utiliser RECHERCHEV.

RECHERCHEV nécessite que la table soit structurée de sorte que les valeurs de recherche apparaissent dans la colonne la plus à gauche. Les données que vous souhaitez récupérer (valeurs de résultat) peuvent apparaître dans n'importe quelle colonne à droite. Lorsque vous utilisez RECHERCHEV, imaginez que chaque colonne du tableau est numérotée, en commençant par la gauche. Pour obtenir une valeur à partir d'une colonne particulière, indiquez simplement le nombre approprié comme «index de colonne». Dans l'exemple ci-dessous, nous voulons rechercher l'adresse e-mail, nous utilisons donc le numéro 4 pour l'index de la colonne:

Vue d'ensemble du fonctionnement de RECHERCHEV



Dans le tableau ci-dessus, les identifiants d'employés se trouvent dans la colonne 1 à gauche et les adresses e-mail dans la colonne 4 à droite.

Pour utiliser RECHERCHEV, vous fournissez 4 informations, ou «arguments»:

  1. La valeur que vous recherchez ( lookup_value )
  2. La plage de cellules qui composent le tableau ( tableau_table )
  3. Le numéro de la colonne à partir de laquelle récupérer un résultat ( column_index )
  4. Le mode match ( range_lookup , TRUE = approximatif, FALSE = exact)

Vidéo: Comment utiliser RECHERCHEV (3 min)

Si vous ne comprenez toujours pas l'idée de base de RECHERCHEV, Jon Acampora, à Excel Campus, a un bonne explication basé sur le menu du café Starbucks.

2. VLOOKUP semble juste

Peut-être que la plus grande limitation de RECHERCHEV est qu'il ne peut regarder que le droit de récupérer des données.

Cela signifie que RECHERCHEV ne peut obtenir des données qu'à partir des colonnes à droite de la première colonne du tableau. Lorsque les valeurs de recherche apparaissent dans la première colonne (la plus à gauche), cette limitation ne signifie pas grand-chose, puisque toutes les autres colonnes sont déjà à droite. Cependant, si la colonne de recherche apparaît quelque part dans le tableau, vous ne pourrez rechercher des valeurs que dans les colonnes à droite de cette colonne. Vous devrez également fournir une table plus petite à VLOOKUP qui commence par la colonne de recherche.

RECHERCHEV ne peut regarder qu'à droite de la colonne de valeur de recherche

Vous pouvez surmonter cette limitation en utilisant INDEX et MATCH au lieu de VLOOKUP.

3. RECHERCHEV trouve la première correspondance

En mode de correspondance exacte, si une colonne de recherche contient des valeurs en double, RECHERCHEV ne correspondra qu'à la première valeur. Dans l'exemple ci-dessous, nous utilisons VLOOKUP pour trouver un prénom et VLOOKUP est configuré pour effectuer une correspondance exacte. Bien qu'il y ait deux 'Janet dans la liste, RECHERCHEV ne correspond qu'à la première:

RECHERCHEV trouve toujours la première correspondance

Remarque: le comportement peut changer lorsque VLOOKUP est utilisé en mode de correspondance approximative. Cet article explique le sujet en détail.

4. RECHERCHEV n'est pas sensible à la casse

Lors de la recherche d'une valeur, RECHERCHEV ne traite pas le texte des majuscules et des minuscules différemment. Pour RECHERCHEV, un code produit comme «PQRF» est identique à «pqrf». Dans l'exemple ci-dessous, nous recherchons des majuscules 'JANET' mais VLOOKUP ne distingue pas la casse, il correspond donc simplement à 'Janet', puisque c'est la première correspondance qu'il trouve:

RECHERCHEV n'est PAS sensible à la casse

Nous proposons également formation rémunérée pour RECHERCHEV et INDEX / MATCH

5. RECHERCHEV a deux modes de correspondance

RECHERCHEV a deux modes de fonctionnement: correspondance exacte et correspondance approximative. Dans la plupart des cas, vous souhaiterez probablement utiliser RECHERCHEV en mode de correspondance exacte. Cela a du sens lorsque vous souhaitez rechercher des informations basées sur une clé unique, par exemple, des informations produit basées sur un code produit ou des données de film basées sur un titre de film:

Exemple de correspondance exacte VLOOKUP - films correspondants

La formule dans H6 pour rechercher l'année basée sur une correspondance exacte du titre du film est:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Cependant, vous voudrez utiliser le mode approximatif dans les cas où vous ne correspondez pas à un identifiant unique, mais que vous recherchez plutôt la «meilleure correspondance» ou la «meilleure catégorie». Par exemple, vous recherchez peut-être l'affranchissement en fonction du poids, le taux d'imposition basé sur le revenu ou le taux de commission basé sur un chiffre de vente mensuel. Dans ces cas, vous ne trouverez probablement pas la valeur de recherche exacte dans le tableau. Au lieu de cela, vous voulez que VLOOKUP vous obtienne la meilleure correspondance pour une valeur de recherche donnée.

Exemple de correspondance approximative VLOOKUP - commissions

La formule dans D5 fait une correspondance approximative pour récupérer la commission correcte:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Attention: RECHERCHEV utilise une correspondance approximative par défaut

La correspondance exacte et approximative dans VLOOKUP est contrôlée par le 4ème argument, appelé «recherche de plage». Ce nom n'est pas intuitif, il vous suffira donc de mémoriser son fonctionnement.

Pour une correspondance exacte, utilisez FALSE ou 0. Pour une correspondance approximative, définissez range_lookup sur TRUE ou 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Malheureusement, le 4ème argument, range_lookup, est facultatif et vaut par défaut TRUE, ce qui signifie que VLOOKUP fera une correspondance approximative par défaut. Lors d'une correspondance approximative, VLOOKUP suppose que la table est triée et effectue une recherche binaire. Lors d'une recherche binaire, si VLOOKUP trouve une valeur de correspondance exacte, il renvoie une valeur de cette ligne. Si toutefois, RECHERCHEV rencontre une valeur supérieure à la valeur de recherche, il renverra une valeur de la ligne précédente.

Il s'agit d'une valeur par défaut dangereuse car de nombreuses personnes laissent involontairement RECHERCHEV dans son mode par défaut, ce qui peut provoquer un résultat incorrect lorsque la table n'est pas triée.

Pour éviter ce problème, assurez-vous d'utiliser FALSE ou zéro comme quatrième argument lorsque vous souhaitez une correspondance exacte.

7. Vous pouvez forcer RECHERCHEV à faire une correspondance exacte

Pour forcer VLOOKUP à trouver une correspondance exacte, assurez-vous de définir l'argument 4 (range_lookup) sur FALSE ou zéro. Ces deux formules sont équivalentes:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

En mode de correspondance exacte, lorsque VLOOKUP ne trouve pas de valeur, il renvoie # N / A. Ceci indique clairement que la valeur n'est pas trouvée dans le tableau.

8. Vous pouvez dire à RECHERCHEV de faire une correspondance approximative

Pour utiliser RECHERCHEV en mode de correspondance approximative, omettez le 4e argument (range_lookup) ou indiquez-le TRUE ou 1. Ces 3 formules sont équivalentes:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Nous vous recommandons de toujours définir explicitement l'argument range_lookup, même si VLOOKUP ne l'exige pas. De cette façon, vous avez toujours un rappel visuel du mode de match que vous attendez.

Vidéo: Comment utiliser RECHERCHEV pour des correspondances approximatives

9. Pour les correspondances approximatives, les données doivent être triées

Si vous utilisez la correspondance en mode approximatif, vos données doit être trié dans l'ordre croissant par valeur de recherche. Sinon, vous pourriez obtenir un résultats incorrects . Sachez également que parfois les données textuelles peuvent voir triés, même si ce n'est pas le cas.

Felienne Hermans a un excellent exemple de ce problème ici , à partir d'une analyse cool qu'elle a faite sur Enron feuilles de calcul!

10. RECHERCHEV peut fusionner des données dans différentes tables

Un cas d'utilisation courant de RECHERCHEV est de joindre les données de deux tables ou plus. Par exemple, vous avez peut-être des données de commande dans une table et des données client dans une autre et vous souhaitez importer certaines données client dans la table de commande pour analyse:

RECHERCHEV: fusionne les données en joignant des tables -avant

Étant donné que l'ID client existe dans les deux tables, vous pouvez utiliser cette valeur pour extraire les données souhaitées avec RECHERCHEV. Configurez simplement RECHERCHEV pour utiliser la valeur d'id dans la table un, et les données dans la table 2, avec l'index de colonne requis. Dans l'exemple ci-dessous, nous utilisons deux formules RECHERCHEV. L'un pour extraire le nom du client et l'autre pour extraire l'état du client.

VLOOKUP fusionne les données en joignant des tables -après

Lien: Exemple de fusion avec RECHERCHEV .

Vidéo: Comment utiliser RECHERCHEV pour fusionner des tables .

11. RECHERCHEV peut classer ou catégoriser les données

Si jamais vous avez besoin d'appliquer des catégories arbitraires à des enregistrements de données, vous pouvez facilement le faire avec RECHERCHEV, en utilisant une table qui agit comme la «clé» pour attribuer des catégories.

Un exemple classique est celui des notes, où vous devez attribuer une note en fonction d'un score:

RECHERCHEV utilisé pour catégoriser - attribuer des notes

Dans ce cas, RECHERCHEV est configuré pour une correspondance approximative, il est donc important que la table soit triée par ordre croissant.

Mais vous pouvez également utiliser RECHERCHEV pour attribuer des catégories arbitraires. Dans l'exemple ci-dessous, nous utilisons RECHERCHEV pour calculer un groupe pour chaque département à l'aide d'une petite table (nommée «clé») qui définit le regroupement.

RECHERCHEV utilisé pour catégoriser - attribuer des groupes arbitraires

12. Les références absolues rendent VLOOKUP plus portable

Dans les situations où vous prévoyez de récupérer des informations à partir de plusieurs colonnes d'une table, ou si vous devez copier et coller RECHERCHEV, vous pouvez gagner du temps et gagner du temps en utilisant des références absolues pour la valeur de recherche et le tableau de la table. Cela vous permet de copier la formule, puis de modifier uniquement le numéro d'index de colonne pour utiliser la même recherche pour obtenir une valeur d'une colonne différente.

Par exemple, étant donné que la valeur de recherche et le tableau de table sont absolus, nous pouvons copier la formule dans les colonnes, puis revenir en arrière et modifier l'index de la colonne si nécessaire.

Les références absolues rendent les formules RECHERCHEV plus portables

13. Les plages nommées rendent VLOOKUP plus facile à lire (et plus portable)

Les plages absolues sont assez laides, elles peuvent donc rendre vos formules RECHERCHEV beaucoup plus propres et plus faciles à lire en remplaçant les références absolues par des plages nommées, qui sont automatiquement absolues.

Par exemple, dans l'exemple de données d'employé ci-dessus, vous pouvez nommer la cellule d'entrée 'id', puis nommer les données dans la table 'data', vous pouvez écrire votre formule comme suit:

Les plages nommées facilitent la lecture des formules RECHERCHEV

Non seulement cette formule est plus facile à lire, mais elle est également plus portable, car les plages nommées sont automatiquement absolues.

14. L'insertion d'une colonne peut casser les formules VLOOKUP existantes

Si vous avez des formules RECHERCHEV existantes dans une feuille de calcul, les formules peuvent être interrompues si vous insérez une colonne dans la table. Cela est dû au fait que les valeurs d'index de colonne codées en dur ne changent pas automatiquement lorsque des colonnes sont insérées ou supprimées.

Dans cet exemple, les recherches pour le rang et les ventes étaient interrompues lorsqu'une nouvelle colonne était insérée entre l'année et le rang. L'année continue de fonctionner car elle se trouve à gauche de la colonne insérée:

L'insertion d'une colonne dans le tableau peut interrompre VLOOKUP

Pour éviter ce problème, vous pouvez calculer un index de colonne comme décrit dans les deux conseils suivants.

15. Vous pouvez utiliser ROW ou COLUMN pour calculer un index de colonne

Si vous êtes du genre à être dérangé par une quantité quelconque de modifications après la copie d'une formule, vous pouvez utiliser ROW ou COLUMN pour générer des index de colonne dynamiques. Si vous obtenez des données à partir de colonnes consécutives, cette astuce vous permet de configurer une formule RECHERCHEV, puis de la copier sans aucune modification.

Par exemple, avec les données d'employés ci-dessous, nous pouvons utiliser la fonction COLUMN pour générer un index de colonne dynamique. Pour la première formule de la cellule C3, COLUMN renverra par elle-même 3 (car la colonne C est la troisième de la feuille de calcul), nous devons donc simplement en soustraire une et copier la formule dans:

Exemple d'utilisation de COLUMN pour calculer l'index de colonne pour VLOOKUP

Toutes les formules sont identiques et aucune post-édition n'est requise.

La formule que nous utilisons est la suivante:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Utilisez VLOOKUP + MATCH pour un index de colonne entièrement dynamique

En poussant encore plus loin le conseil ci-dessus, vous pouvez utiliser MATCH pour rechercher la position d'une colonne dans une table et renvoyer un index de colonne entièrement dynamique.

Ceci est parfois appelé une recherche bidirectionnelle puisque vous recherchez à la fois la ligne et la colonne.

Un exemple serait de rechercher les ventes d'un vendeur au cours d'un mois particulier ou de rechercher le prix d'un produit particulier auprès d'un fournisseur particulier.

Par exemple, supposons que vous ayez des ventes par mois, ventilées par vendeur:

RECHERCHEV RECHERCHE bidirectionnelle - comment rechercher le mois?

RECHERCHEV peut facilement trouver le vendeur, mais il n'a aucun moyen de gérer automatiquement le nom du mois. L'astuce consiste à utiliser la fonction MATCH à la place d'un index de colonne statique.

RECHERCHEV Recherche bidirectionnelle à l'aide de MATCH pour obtenir l'index de la colonne

Notez que nous donnons à match une plage qui inclut toutes les colonnes de la table afin de «synchroniser» les numéros de colonne utilisés par RECHERCHEV.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Remarque: vous verrez souvent des recherches bidirectionnelles effectuées avec INDEX et MATCH, une approche qui offre plus de flexibilité et de meilleures performances sur les grands ensembles de données. Voyez comment dans cette rapide vidéo: Comment faire une recherche bidirectionnelle avec INDEX et MATCH .

17. RECHERCHEV autorise les caractères génériques pour une correspondance partielle

Chaque fois que vous utilisez RECHERCHEV en mode de correspondance exacte, vous avez la possibilité d'utiliser des caractères génériques dans la valeur de recherche. Cela peut sembler contre-intuitif, mais les jokers vous permettent de faire une correspondance exacte basée sur une correspondance partielle :)

Excel fournit deux caractères génériques: un astérisque (*) correspond à un ou plusieurs caractères et un point d'interrogation (?) Correspond à un caractère.

Par exemple, vous pouvez taper un astérisque directement dans une cellule et y faire référence en tant que valeur de recherche avec RECHERCHEV. Dans l'écran ci-dessous, nous avons entré «Mon *» dans H3, qui est une plage nommée appelée «val». Cela provoque VLOOKUP pour correspondre au nom «Monet».

RECHERCHEV avec des caractères génériques - en utilisant directement un astérisque

La formule dans ce cas est simple:

 
= VLOOKUP (val,data,1,0)

Si vous le souhaitez, vous pouvez ajuster la formule RECHERCHEV pour utiliser un caractère générique intégré, comme l'exemple ci-dessous, où nous concaténons simplement la valeur dans H3 avec un astérisque.

RECHERCHEV avec des caractères génériques - l'astérisque est concaténé à la valeur de recherche

Dans ce cas, nous concaténons l'astérisque à la valeur de recherche dans la fonction RECHERCHEV:

 
= VLOOKUP (val&'*',data,1,0)

Remarque: soyez prudent avec les caractères génériques et RECHERCHEV. Ils vous permettent de créer facilement une «correspondance paresseuse», mais ils permettent également de trouver facilement la mauvaise correspondance.

18. Vous pouvez intercepter les erreurs # N / A et afficher un message convivial

En mode de correspondance exacte, VLOOKUP affichera l'erreur # N / A lorsqu'aucune correspondance n'est trouvée. D'une certaine manière, cela est utile car cela vous indique définitivement qu'il n'y a pas de correspondance dans la table de recherche. Cependant, les erreurs # N / A ne sont pas très amusantes à regarder, il existe donc plusieurs façons de piéger cette erreur et d'afficher autre chose à la place.

Une fois que vous commencez à utiliser RECHERCHEV, vous êtes obligé de rencontrer l'erreur # N / A, qui se produit lorsque RECHERCHEV ne parvient pas à trouver une correspondance.

comment supprimer le symbole dans Excel

C'est une erreur utile, car VLOOKUP vous indique clairement qu'il ne peut pas trouver la valeur de recherche. Dans cet exemple, 'Latte' n'existe pas en tant que boisson dans la table, donc RECHERCHEV renvoie une erreur # N / A

RECHERCHEV arborant une erreur # N / A

La formule dans ce cas est une correspondance exacte tout à fait standard:

 
= VLOOKUP (E6,data,2,0)

Cependant, les erreurs # N / A ne sont pas très amusantes à regarder, vous voudrez peut-être attraper cette erreur et afficher un message plus convivial.

Le moyen le plus simple pour intercepter les erreurs avec RECHERCHEV est d'encapsuler RECHERCHEV dans la fonction IFERROR. IFERROR vous permet de «détecter» toute erreur et de renvoyer un résultat de votre choix.

Pour intercepter cette erreur et afficher un message `` introuvable '' à la place de l'erreur, vous pouvez simplement envelopper la formule originale dans IFERROR et définir le résultat souhaité:

Erreur VLOOKUP # N / A interceptée avec IFERROR

Si la valeur de recherche est trouvée, aucune erreur ne se produit et la fonction RECHERCHEV renvoie un résultat normal. Voici la formule:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Les nombres sous forme de texte peuvent provoquer une erreur de correspondance

Parfois, la table avec laquelle vous travaillez dans RECHERCHEV peut contenir des nombres entrés sous forme de texte. Si vous récupérez simplement des nombres sous forme de texte à partir d'une colonne dans un tableau, cela n'a pas d'importance. Mais si la première colonne du tableau contient des nombres entrés sous forme de texte, vous obtiendrez une erreur # N / A si la valeur de recherche n'est pas également du texte.

Dans l'exemple suivant, les identifiants de la table planétaire sont des nombres saisi sous forme de texte , ce qui entraîne le renvoi d'une erreur de RECHERCHEV, car la valeur de recherche est le numéro 3:

Numéros saisis sous forme de texte Exemple d'erreur RECHERCHEV

Pour résoudre ce problème, vous devez vous assurer que la valeur de recherche et la première colonne de la table sont toutes les deux du même type de données (les deux nombres ou les deux textes).

Une façon de procéder consiste à convertir les valeurs de la colonne de recherche en nombres. Un moyen simple de le faire est d'ajouter zéro à l'aide de la pâte spéciale.

Si vous n'avez pas de contrôle facile sur la table source, vous pouvez également ajuster la formule RECHERCHEV pour convertir la valeur de recherche en texte en concaténant `` '' en la valeur comme suit:

 
= VLOOKUP (id&'',planets,2,0)

Numéros saisis sous forme de texte Solution d'erreur RECHERCHEV

Si vous ne pouvez pas être certain quand vous aurez des nombres et quand vous aurez du texte, vous pouvez répondre aux deux options en encapsulant RECHERCHEV dans IFERROR et en écrivant une formule pour gérer les deux cas:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. Vous pouvez utiliser RECHERCHEV pour remplacer les instructions IF imbriquées

L'une des utilisations les plus intéressantes de VLOOKUP est de remplacer les instructions IF imbriquées. Si vous avez déjà construit une série de IF imbriquées, vous savez qu'elles fonctionnent bien, mais elles nécessitent un peu de bagarre entre parenthèses. Vous devez également faire attention à l'ordre dans lequel vous travaillez, afin de ne pas introduire d'erreur logique.

Par exemple, une utilisation courante des IF imbriquées consiste à attribuer des notes en fonction d'un score quelconque. Dans l'exemple ci-dessous, vous pouvez voir qu'une formule a été créée avec des IF imbriquées pour cela, en utilisant la clé de note à droite comme guide.

Attribution de notes avec une longue formule IF imbriquée

La formule IF imbriquée complète ressemble à ceci:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Cela fonctionne bien, mais notez que la logique et les scores réels sont intégrés directement dans la formule. Si le score change pour une raison quelconque, vous devrez mettre à jour soigneusement une formule, puis la copier dans tout le tableau.

En revanche, RECHERCHEV peut attribuer les mêmes notes avec une formule simple. Tout ce que vous avez à faire est de vous assurer que le tableau des clés de note est configuré pour RECHERCHEV (c'est-à-dire qu'il doit être trié par score et contient des crochets pour gérer tous les scores).

Après avoir défini une plage nommée `` clé '' pour la table de clé de note, la formule RECHERCHEV est très simple et a généré les mêmes notes que la formule IF imbriquée d'origine:

Attribuer des notes avec une simple formule RECHERCHEV

Avec la table de clé de grade nommée `` clé '', nous avons une formule RECHERCHEV très simple:

 
= VLOOKUP (C5,key,2,TRUE)

Un avantage intéressant de cette approche est que la logique et les scores sont intégrés directement dans le tableau des clés de note. Si quelque chose change, vous pouvez simplement mettre à jour la table directement et les formules RECHERCHEV se mettront à jour automatiquement - aucune modification n'est requise.

Vidéo: Comment remplacer les IF imbriqués par VLOOKUP

21. RECHERCHEV ne peut gérer qu'un seul critère

De par sa conception, RECHERCHEV ne peut trouver des valeurs que sur la base d'un seul critère, qui est fourni en tant que valeur de recherche à rechercher dans la première colonne de la table (la colonne de recherche).

Cela signifie que vous ne pouvez pas facilement faire des choses comme rechercher un employé avec le nom de famille «Smith» dans «Comptabilité», ou rechercher un employé en fonction du prénom et du nom dans des colonnes séparées.

Cependant, il existe des moyens de surmonter cette limitation. Une solution de contournement consiste à créer une colonne d'assistance qui concatène les valeurs de différentes colonnes pour créer des valeurs de recherche qui se comportent comme plusieurs conditions. Par exemple, ici, nous voulons trouver le service et le groupe d'un employé, mais le prénom et le nom apparaissent dans des colonnes séparées. Comment pouvons-nous rechercher les deux à la fois?

VLOOKUP problème de critères multiples - comment rechercher à la fois le prénom et le nom?

Tout d'abord, ajoutez une colonne d'aide qui concatène simplement les noms et prénoms ensemble:

RECHERCHEV plusieurs critères étape 2 - ajouter une colonne d'assistance qui rejoint plusieurs critères

Ensuite, configurez RECHERCHEV pour utiliser une table qui inclut cette nouvelle colonne et joignez les noms et prénoms pour la valeur de recherche:

RECHERCHEV plusieurs critères Étape 3 - Joindre des critères pour former une valeur de recherche

La formule finale VLOOKUP recherche le prénom et le nom ensemble en utilisant la colonne d'assistance comme clé:

 
= VLOOKUP (C3&D3,data,4,0)

22. Deux VLOOKUPS sont plus rapides qu'un VLOOKUP

Cela peut sembler complètement fou, mais lorsque vous avez un grand ensemble de données et que vous avez besoin de faire une correspondance exacte, vous pouvez beaucoup accélérer VLOOKUP en ajoutant un autre VLOOKUP à la formule!

Le contexte: imaginez que vous avez beaucoup de données de commande, disons plus de 10 000 enregistrements et que vous utilisez RECHERCHEV pour rechercher le total de la commande en fonction de l'ID de la commande. Donc, vous utilisez quelque chose comme ceci:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

Le FALSE à la fin oblige RECHERCHEV à faire une correspondance exacte. Vous voulez une correspondance exacte, car il est possible qu'un numéro de commande ne soit pas trouvé. Dans ce cas, le paramètre de correspondance exacte entraînera VLOOKUP renvoyer l'erreur # N / A.

Le problème est que les correspondances exactes sont vraiment lentes, car Excel doit procéder de manière linéaire à travers toutes les valeurs jusqu'à ce qu'il trouve une correspondance ou non.

À l'inverse, les correspondances approximatives sont ultra-rapides car Excel est capable de faire ce qu'on appelle un recherche binaire .

Le problème avec les recherches binaires cependant (c'est-à-dire RECHERCHEV en mode de correspondance approximative) est que RECHERCHEV peut renvoyer le mauvais résultat lorsqu'une valeur n'est pas trouvée. Pire encore, le résultat peut sembler tout à fait normal, il peut donc être très difficile à repérer.

La solution consiste à utiliser RECHERCHEV deux fois, les deux fois en mode de correspondance approximative. La première instance vérifie simplement que la valeur existe réellement. Si tel est le cas, une autre RECHERCHEV est exécutée (encore une fois, en mode de correspondance approximative) pour récupérer les données souhaitées. Sinon, vous pouvez renvoyer toute valeur que vous souhaitez indiquer qu'un résultat n'a pas été trouvé.

La formule finale ressemble à ceci:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

J'ai appris cette approche de Charles Williams de FastExcel, qui a un article fantastique et détaillé ici: Pourquoi 2 VLOOKUPS valent mieux qu'un VLOOKUP .

Remarque: vos données doivent être triées pour utiliser cette astuce. C'est simplement un moyen de se protéger contre une valeur de recherche manquante, tout en maintenant une recherche rapide.

23. INDEX et MATCH peuvent faire ensemble tout ce que RECHERCHEV peut faire et plus

Si vous suivez Excel en ligne, vous rencontrerez probablement le VLOOKUP vs débat INDEX / MATCH. L'argument peut être étonnamment chauffé :)

L'essentiel est le suivant: INDEX + MATCH peut faire tout ce que VLOOKUP (et HLOOKUP) peut faire, avec beaucoup plus de flexibilité, au prix d'un peu plus de complexité. Donc, ceux qui sont en faveur d'INDEX + MATCH soutiendront (très sainement) que vous pourriez aussi bien commencer à apprendre INDEX et MATCH, car cela vous donne un meilleur ensemble d'outils à la fin.

L'argument contre INDEX + MATCH est qu'il nécessite deux fonctions au lieu d'une, donc il est intrinsèquement plus complexe pour les utilisateurs (en particulier les nouveaux utilisateurs) d'apprendre et de maîtriser.

Mon avis est que si vous utilisez fréquemment Excel, vous voudrez apprendre à utiliser INDEX et MATCH. C'est une combinaison très puissante.

Mais je pense aussi que vous devriez apprendre VLOOKUP, que vous rencontrerez partout, souvent dans des feuilles de calcul dont vous héritez des autres. Dans des situations simples, VLOOKUP fera le travail très bien sans problème.

Pour en savoir plus sur INDEX et MATCH, voir cet article .

Auteur Dave Bruns


^