Ces exemples vous apprendront comment rechercher plusieurs critères, renvoyer une instance spécifique ou toutes les correspondances, effectuer une recherche dynamique dans plusieurs feuilles, et plus.
Cest la deuxième partie de la série qui vous aidera à exploiter la puissance dExcel VLOOKUP. Les exemples impliquent que vous savez comment cette fonction fonctionne. Sinon, il va de soi de commencer par les utilisations de base de RECHERCHEV dans Excel.
Avant daller plus loin, permettez-moi de vous rappeler brièvement la syntaxe:
Maintenant que tout le monde est sur la même page, examinons de plus près les exemples de formules VLOOKUP avancées:
- Comment rechercher plusieurs critères dans Excel
- Vlookup et renvoie la nième correspondance
- Vlookup plusieurs valeurs
- Vlookup basé sur les valeurs de ligne et de colonne
- Formule Vlookup imbriquée
- RECHERCHEV et INDIRECT pour extraire des données de plusieurs feuilles
Comment rechercher plusieurs critères
La fonction RECHERCHEV Excel est vraiment utile lorsquil sagit de rechercher dans une base de données pour une certaine valeur. Cependant, il lui manque une fonctionnalité importante – sa syntaxe ne permet quune seule valeur de recherche. Mais que se passe-t-il si vous souhaitez effectuer une recherche avec plusieurs conditions? Vous avez le choix entre plusieurs solutions.
Formule 1. RECHERCHEV basée sur tw o valeurs
Supposons que vous ayez une liste de commandes et que vous souhaitiez trouver la quantité en fonction de 2 critères, le nom du client et le produit. Un facteur de complication est que chaque client a commandé plusieurs produits, comme indiqué dans le tableau ci-dessous:
Une formule RECHERCHEV habituelle ne fonctionnera pas dans ce cas situation car elle renvoie la première correspondance trouvée basée sur une seule valeur de recherche que vous spécifiez.
Pour surmonter cela, vous pouvez ajouter une colonne dassistance et concaténer les valeurs de deux colonnes de recherche (Client et Produit). Il est important que la colonne dassistance soit la colonne la plus à gauche du tableau car cest là que RECHERCHEV Excel recherche toujours la valeur de recherche.
Alors, ajoutez une colonne à gauche de votre tableau et copiez la formule ci-dessous dans cette colonne. Cela remplira la colonne dassistance avec les valeurs des colonnes B et C (le caractère despace est concaténé entre les deux pour une meilleure lisibilité):
=B2&" "&C2
Ensuite, utilisez une formule VLOOKUP standard et placez les deux critères dans largument lookup_value, séparés par un espace:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Ou, entrez les critères dans des cellules séparées (G1 et G2 dans notre cas) et concaténez ces cellules:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Comme nous voulez retourner une valeur de la colonne D, qui est la quatrième dans le tableau de la table, nous utilisons 4 pour col_index_num. Largument range_lookup est défini sur FALSE pour Vlookup une correspondance exacte. La capture décran ci-dessous montre le résultat:
Si votre table de recherche est dans une autre feuille, incluez le nom de la feuille dans votre formule RECHERCHEV. Par exemple:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Vous pouvez également créer une plage nommée pour la table de recherche (par exemple, Commandes) pour faciliter la formule- à lire:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Pour plus dinformations, veuillez consulter Comment Vlookup à partir dune autre feuille dans Excel.
Formule 2. RECHERCHEV Excel avec plusieurs conditions
En théorie, vous pouvez utiliser lapproche ci-dessus pour Vlookup sur plus de deux critères. H Cependant, il y a quelques mises en garde. Premièrement, une valeur de recherche est limitée à 255 caractères, et deuxièmement, la conception de la feuille de calcul peut ne pas autoriser lajout dune colonne daide.
Heureusement, Microsoft Excel fournit souvent plusieurs façons de faire la même chose. Pour rechercher plusieurs critères, vous pouvez utiliser soit une combinaison INDEX MATCH, soit la fonction XLOOKUP récemment introduite dans Office 365.
Par exemple, pour rechercher en fonction de 3 valeurs différentes (Date, Nom du client et Produit), utilisez lune des formules suivantes:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Où:
Pour une explication détaillée des formules, veuillez consulter:
- XLOOKUP avec plusieurs critères
- INDEX MATCH formule avec plusieurs critères
Comment utiliser RECHERCHEV pour obtenir la 2ème, 3ème ou la nième correspondance
Comme vous le savez déjà, Excel VLOOKUP ne peut récupérer quune seule valeur correspondante, plus précisément, il renvoie la première correspondance trouvée. Mais que se passe-t-il sil y a plusieurs correspondances dans votre tableau de recherche et que vous souhaitez obtenir la deuxième ou la troisième instance? La tâche semble assez complexe, mais la solution existe!
Formule 1. Vlookup Nième instance
Supposons que vous ayez des noms de clients dans une colonne, les produits quils ont achetés dans une autre, et vous cherchent à trouver le 2ème ou 3ème produit acheté par un client donné.
Le moyen le plus simple est dajouter une colonne daide à la gauche du tableau comme nous lavons fait dans le premier exemple. Mais cette fois, nous allons le remplir avec des noms de clients et des numéros doccurrence comme « John Doe1 », « John Doe2 », etc.
Pour obtenir loccurrence, utilisez la fonction COUNTIF avec une référence de plage mixte (le la première référence est absolue et la seconde est relative comme $ B $ 2: B2). Étant donné que la référence relative change en fonction de la position de la cellule où la formule est copiée, à la ligne 3, elle deviendra $ B $ 2: B3, à la ligne 4 – $ B $ 2: B4, et ainsi de suite.
Concaténée avec le nom du client (B2), la formule prend la forme suivante:
=B2&COUNTIF($B$2:B2, B2)
La formule ci-dessus va à A2, et puis copiez-le dans autant de cellules que nécessaire.
Après cela, saisissez le nom de la cible et le numéro doccurrence dans des cellules séparées (F1 et F2), et utilisez la formule ci-dessous pour rechercher une occurrence spécifique:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formule 2. 2e occurrence de Vlookup
Si vous recherchez la 2ème instance de la valeur de recherche, vous pouvez vous passer de la colonne dassistance. Au lieu de cela, créez le tableau de table dynamiquement en utilisant la fonction INDIRECT avec MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Où:
- E1 est la valeur de recherche
- A2: A11 est la plage de recherche
- B11 est la dernière cellule (en bas à droite) de la table de recherche
Veuillez noter que la formule ci-dessus est écrite pour un cas spécifique où les cellules de données de la table de recherche commencent à la ligne 2. Si votre table se trouve quelque part dans au milieu de la feuille, utilisez cette formule universelle, où A1 est la cellule en haut à gauche de la table de recherche contenant un en-tête de colonne:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Comment fonctionne cette formule
Voici la partie clé de la formule qui crée une plage de recherche dynamique:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
La fonction MATCH configurée pour une correspondance exacte (0 dans le dernier argument) compare le nom cible (E1) à la liste des noms (A2: A11) et renvoie la position de la première correspondance trouvée, qui est 3 en o votre cas. Ce nombre va être utilisé comme coordonnée de ligne de départ pour la plage vlookup, nous y ajoutons donc 2 (+1 pour exclure la première instance et +1 pour exclure la ligne 1 avec les en-têtes de colonne). Vous pouvez également utiliser 1 + ROW (A1) pour calculer lajustement nécessaire automatiquement en fonction de la position de la ligne den-tête (A1 dans notre cas).
En conséquence, nous obtenons la chaîne de texte suivante, que INDIRECT convertit en une référence de plage:
INDIRECT("A"&5&":B11") -> A5:B11
Cette plage va à largument table_array de VLOOKUP, le forçant à commencer la recherche dans la ligne 5, en omettant la première instance de la valeur de recherche:
VLOOKUP(E1, A5:B11, 2, FALSE)
Comment Vlookup et renvoyer plusieurs valeurs dans Excel
La fonction Excel RECHERCHEV est conçue pour renvoyer une seule correspondance. Existe-t-il un moyen de Vlookup plusieurs instances? Oui, il y en a, mais pas facile. Cela nécessite une utilisation combinée de plusieurs fonctions telles que INDEX, SMALL et ROW est une formule matricielle.
Par exemple, ce qui suit peut trouver toutes les occurrences de la valeur de recherche F2 dans la plage de recherche B2: B16 et retourner plusieurs correspondances de la colonne C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Il y a 2 façons de saisir la formule dans votre feuille de calcul:
- Tapez la formule dans la première cellule, appuyez sur Ctrl + Maj + Entrée, puis faites-la glisser vers quelques cellules supplémentaires.
- Sélectionnez plusieurs cellules adjacentes dans une seule colonne (F1: F11 dans la capture décran ci-dessous), tapez la formule et appuyez sur Ctrl + Maj + Entrée pour la terminer.
Dans tous les cas, le nombre de cellules dans lesquelles vous entrez la formule doit être égal ou supérieur à le nombre maximum de correspondances possibles.
Pour une explication détaillée de la logique de formule et plus dexemples, veuillez consulter Comment RECHERCHER plusieurs valeurs dans Excel .
Comment Vlookup en lignes et en colonnes (recherche bidirectionnelle)
La recherche bidirectionnelle (aka recherche de matrice ou recherche bidimensionnelle) est un mot sophistiqué pour rechercher une valeur à lintersection dune certaine ligne et colonne.Il existe plusieurs façons différentes de faire une recherche bidimensionnelle dans Excel, mais comme ce didacticiel se concentre sur la fonction RECHERCHEV, nous lutilisons naturellement.
Pour cet exemple, nous allons prendre le ci-dessous le tableau avec les ventes mensuelles et élaborez une formule RECHERCHEV pour récupérer le chiffre des ventes dun article spécifique au cours dun mois donné.
Avec les noms darticles en A2: A9, les noms de mois en B1: F1, larticle cible dans I1 et le mois cible dans I2, la formule est la suivante:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Comment cette formule fonctionne
Le cœur de la formule est la fonction VLOOKUP standard qui recherche une correspondance exacte avec la valeur de recherche dans I1. Mais puisque nous ne savons pas dans quelle colonne se trouvent exactement les ventes pour un mois spécifique, nous ne pouvons pas fournir le numéro de colonne directement à largument col_index_num. Pour trouver cette colonne, nous utilisons la fonction MATCH suivante:
MATCH(I2, A1:F1, 0)
Traduit en anglais, la formule dit: recherche la valeur I2 dans A1: F1 et renvoie sa position relative dans le tableau. En fournissant 0 au 3ème argument, vous demandez à MATCH de trouver la valeur exactement égale à la valeur de recherche (cest comme utiliser FALSE pour largument range_lookup de VLOOKUP).
Puisque Mar est dans la 4ème colonne dans le tableau de recherche, la fonction MATCH renvoie 4, qui va directement à largument col_index_num de VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Veuillez faire attention que bien que les noms de mois commencent dans la colonne B, nous utilisons A1: I1 pour le tableau de recherche. Ceci est fait pour que le nombre retourné par MATCH corresponde à la position de la colonne dans table_array de RECHERCHEV.
Pour en savoir plus sur les moyens deffectuer une recherche de matrice dans Excel, veuillez consulter INDEX MATCH MATCH et dautres formules pour une recherche en 2 dimensions.
Comment faire plusieurs Vlookup dans Excel (Vlookup imbriqué)
Parfois, il peut arriver que votre table principale et votre table de recherche naient pas une seule colonne en commun, ce qui vous empêche de faire un Vlookup entre deux tables. Cependant, il existe une autre table, qui ne contient pas les informations que vous recherchez mais a une colonne commune avec la table principale et une autre colonne commune avec la table de recherche.
Limage ci-dessous illustre la situation:
Lobjectif est de copier les prix dans le tableau principal en fonction des ID darticle. Le problème est que le tableau contenant les prix na pas les ID darticle, ce qui signifie que nous devrons faire deux Vlookups dans une formule.
Pour des raisons de commodité, créons dabord quelques plages nommées :
- La table de recherche 1 est nommée Produits (D3: E3).
- La table de recherche 2 est nommée Prix (G3: H3).
Les tables peuvent être dans la même feuille de calcul ou dans des feuilles de calcul différentes.
Et maintenant, nous allons effectuer le soi-disant double Vlookup, alias Vlookup imbriqué.
Dabord, effectuez une RECHERCHEV formule pour trouver le nom du produit dans le tableau de recherche 1 (nommé Produits) en fonction de lID de larticle (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Ensuite, placez la formule ci-dessus dans largument lookup_value dune autre fonction RECHERCHEV pour extraire les prix de la table de recherche 2 (nommée Prix) en fonction du nom du produit renvoyé par la RECHERCHEV imbriquée:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
La capture décran ci-dessous montre notre formule Vlookup imbriquée en action:
Comment Vlookup multiple feuilles dynamiquement
Parfois, vous pouvez avoir des données dans le même format réparties sur plusieurs feuilles de calcul. Et votre objectif est dextraire des données dune feuille spécifique en fonction de la valeur de clé dans une cellule donnée.
Cela peut être plus facile à comprendre à partir dun exemple. Supposons que vous disposiez de quelques rapports de ventes régionaux dans le même format et que vous cherchiez à obtenir les chiffres des ventes dun produit spécifique dans certaines régions:
Comme dans lexemple précédent, nous commençons par définir quelques noms:
- Plage A2: B5 dans la feuille CA est nommée CA_Sales.
- Plage A2 : B5 dans la feuille FL est nommé FL_Sales.
- La plage A2: B5 dans la feuille KS est appelée KS_Sales.
Comme vous pouvez le voir, toutes les plages nommées ont un part (Ventes) et pièces uniques (CA, FL, KS). Veuillez vous assurer de nommer vos plages de la même manière que cela est essentiel pour la formule que nous allons créer.
Formule 1. INDIRECT VLOOKUP pour extraire dynamiquement des données de différentes feuilles
Si votre tâche consiste à récupérer des données de plusieurs feuilles, une formule VLOOKUP INDIRECT est la meilleure solution – compacte et facile à comprendre.
Pour cet exemple, nous organisons le tableau récapitulatif de cette manière:
- Saisissez les produits dintérêt en A2 et A3. Ce sont nos valeurs de recherche.
- Entrez les parties uniques des plages nommées dans B1, C1 et D1.
Et maintenant, nous concaténons la cellule contenant la partie unique (B1) avec la partie commune (« _Sales »), et transmettons la chaîne résultante à INDIRECT:
INDIRECT(B$1&"_Sales")
La fonction INDIRECT transforme la chaîne en un nom quExcel peut comprendre, et vous le placez dans largument table_array de RECHERCHEV:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
La formule ci-dessus va à B2, puis vous la copiez vers le bas et vers la droite.
Veuillez faire attention à ce que, dans la valeur de recherche ($ A2), nous avons verrouillé la coordonnée de la colonne avec une référence de cellule absolue afin que la colonne reste fixe lorsque la formule est copiée vers la droite. Dans la référence B $ 1, nous avons verrouillé la ligne parce que nous voulons la colonne coordonnée pour changer et fournir une partie de nom appropriée à INDIRECT en fonction de la colonne dans laquelle la formule est copiée:
Si votre table principale est organisée différemment, les valeurs de recherche dans une ligne et les parties uniques des noms de plage dans une colonne mn, alors vous devez verrouiller la coordonnée de ligne dans la valeur de recherche (B $ 1) et la coordonnée de colonne dans les parties du nom ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formule 2. RECHERCHEV et IF imbriquées pour rechercher plusieurs feuilles
Dans la situation où vous venez de deux ou trois feuilles de recherche, vous pouvez utiliser une formule RECHERCHEV assez simple avec des fonctions IF imbriquées pour sélectionner la feuille correcte en fonction de la valeur de clé dans une cellule particulière:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Où $ A2 est la valeur de recherche (nom de lélément) et B $ 1 est la valeur clé (état):
Dans ce cas, vous navez pas nécessairement besoin de définir des noms et pouvez utiliser des références externes pour faire référence à une autre feuille ou un autre classeur.
Pour plus dexemples de formules, veuillez consulter Comment rechercher sur plusieurs feuilles dans Excel .
Voilà comment utiliser RECHERCHEV dans Excel. Je vous remercie davoir lu et jespère vous voir sur notre blog la semaine prochaine!
Cahier dexercices à télécharger
Exemples de formule RECHERCHEV avancée (fichier .xlsx)
- Tutoriel RECHERCHEV Excel pour les débutants
- Comment rechercher plusieurs correspondances dans Excel
- Comment utiliser RECHERCHEV & les fonctions SUM ou SUMIF dans Excel
- INDEX & Fonctions MATCH dans Excel – une meilleure alternative à VLOOKUP
- 4 façons de faire un Vlookup sensible à la casse dans Excel
- Excel VLOOKUP ne fonctionne pas – Erreurs N / A, NAME et VALUE