[Formation vidéo gratuite] Tout (absolument tout !) savoir sur la formule RECHERCHEV()

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Bonjour et bienvenue pour cette nouvelle formation, dans laquelle nous allons aborder la formule RECHERCHEV de fond en comble. Cette formule permet de trouver et d’extraire des informations pertinentes dans un tableau en recherchant une valeur donnée dans une colonne (recherche verticale). Tous les principes que nous allons voire sont évidemment valables pour sa petite sœur, la formule RECHERCHEH (pour effectuer une recherche horizontale).

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

Pour suivre cette formation dans de bonnes conditions, je vous invite à télécharger le fichier de travail dans le lien disponible en fin d’article (cliquez ici pour y accéder). Le bouton « RAZ » permet de supprimer les formules.

 

1. Présentation

 

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

La formule RECHERCHEV() admet trois paramètres plus un quatrième qui est facultatif :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;[valeur_proche])
  • Valeur_cherchée : Il s’agit de la valeur à chercher dans la première colonne du tableau. Il peut s'agir soit d'un nombre soit d'un texte ou encore d'une référence à une autre cellule qui va renvoyer la valeur à rechercher,
  • Table_matrice : correspond au tableau sur lequel nous allons effectuer la recherche,
  • No_index_col : correspond au numéro de la colonne du tableau dans laquelle se trouve la valeur que nous allons vouloir récupérer,
  • Valeur_proche : permet de spécifier si l'on souhaite effectuer une recherche approximative (c'est à dire qu'Excel va chercher une valeur qui se rapproche de la valeur cherchée mais qui n'est pas forcément strictement égal) ou on peut également spécifier que cette valeur doit être exact (et donc si Excel n'arrive pas à trouver le résultat exact il renverra une erreur).

 

2. Applications simples

 

   2.1. Premier exemple

 

Voyons un petit exemple pour comprendre le formulenement de cette formule. Dans cet exemple, nous avons un tableau qui reprend dans une première colonne les noms d’un certain nombre de pays (la Belgique, l’Espagne, la France et l’Italie) et les capitales de chacun de ces pays dans une seconde colonne.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Si nous voulons retrouver la capitale de la France nous allons donc utiliser la formule RECHERCHEV() de la manière suivante :

=RECHERCHEV("France";B7:C11;2)
  • "France" : Nous souhaitons retrouver la capitale de la France, il suffit donc de saisir au choix les coordonnées d’une cellule qui contient le terme à retrouver, soit directement une chaine de caractère, saisie entre guillemets ("France").
  • B7:C11 : correspond aux coordonnées du tableau des pays et des capitales (sans les en-têtes),
  • 2 : est le numéro de la colonne au sein du tableau dans laquelle se trouve l’information que nous souhaitons extraire. Il s’agit ici de la seconde colonne du tableau, avec les capitale

Pour ce premier exemple, nous omettons de spécifier le quatrième paramètre (Valeur_proche), afin que la recherche se fasse sur la valeur exacte, et à défaut sur la valeur la plus proche, sans générer d’erreur.

Maintenant si nous voulons effectuer la même recherche mais cette fois ci pour obtenir la capitale de la Finlande, qui ne figure pas dans la liste, alors :

  • Si Valeur_proche est VRAI (ou omis) comme sur l’exemple précédent, alors la formule RECHERCHEV() renverra quand même un résultat (« Madrid »). En effet, la formule analyse les données en suivant un ordre alphabétique, et renverra le dernier résultat qui est inférieur ou égal à la valeur recherché (juste avant la France, nous avons l’Espagne, et sa capitale Madrid).
  • Si par contre, nous ne souhaitons pas effecter une recherche approximatique (FAUX au quatrième paramètre), alors la formule va nous retourner une erreur (#N/A). Il est possible de capter cette erreur en utilisant une seconde formule : la formule SIERREUR() :
=SIERREUR(RECHERCHEV("Finlande";$B$25:$C$28;2;FAUX);"Pays non trouvé")

 

   2.2. Insérer une liste déroulante

 

Pour simplifier la saisie de nos données nous pouvons utiliser la formulenalité « Validation des données » qui va nous permettre d'avoir un menu de choix déroulant.

Pour ce nouvel exemple, nous allons utiliser un tableau qui reprend les ventes trimestrielles par région. Nous souhaitons que la saisie de la valeur_cherchée soit automatisée, pour cela nous utilisons la formule RECHERCHEV() de la manière suivante :

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

=RECHERCHEV(A50;$A$39:$F$42;6;FAUX)
  • A50 : correspond à la cellule dans laquelle se trouve le nom de la région,
  • $A$39:$F$42 : correspond aux coordonnées de notre tableau des ventes trimestrielles par région, exclusion faite des titres de colonne,
  • 6 : la sixième colonne de notre tableau reprend les TOTAUX annuels (somme des quatres trimestres)
  • FAUX : nous souhaitons effectuer une recherche exacte, avec au besoin un message d’erreur

Nous allons maintenant ajouter un menu de choix déroulant sur la cellule ayant pour coordonnées « A50 » :

  • Nous commençons par sélectionner cette cellule,
  • Dans le Ruban, onglet « Données » > « Validation de données »

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

  • Dans la liste « Autoriser », nous sélectionnons le choix « Liste »,
  • Puis dans le champ « Source », nous allons sélectionner les cellules dans lesquelles se trouvent les noms de régions,

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

  • Enfin, nous validons avec entrée

Par la suite, lorsque nous sélectionnons la cellule A50, un bouton apparaît pour sélectionner rapidement la région qui nous intéresse.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

   2.3. Nommer les plages pour gagner du temps

 

Toujours dans l’optique de gagner du temps et de simplifier l’utilisation de formule, il est vivement conseillé d'utiliser des plages nommées qui permettent d'appeler plus facilement des cellules ou groupes de cellules dans une feuille de calcul.

Dans le cadre de notre exemple, nous allons sélectionner le tableau des données (sans les titres de colonnes), puis nous allons venir saisir un nom à donner à cette plage dans la « zone de nom », par exemple = « _ventesParRegion ». Il n’est pas possible d’utiliser d’espace, et mieux vaut éviter les accents pour éviter tout problème (le tiret en début en nom est une astuce qui permet d’afficher l’ensemble des plages nommés et saisissant ce premier caractère). Puis nous validons avec la touche « Entrée ».

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Ensuite, lorsque nous saisissons notre formule, il nous suffira d’appeler notre tableau non par ses coordonnées, mais directement par son nom :

=RECHERCHEV(A51;_ventesParRegion;6)

 

   2.4. Imbriquer RECHERCHEV() dans un calcul

 

Bien entendu il est tout à fait possible d'insérer la formule RECHERCHEV() dans un calcul (ou une autre formule). Imaginons ici que nos ventes soient exprimées en CA TTC. Pour obtenir le montant du CA hors TVA, il suffit d’effectuer le calcul directement sur le résultat retourné par RECHERCHEV() :

=RECHERCHEV(A54;_ventesParRegion;6)/1,2

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

3. Jouer avec la valeur_proche

 

Nous avons déjà étudié dans les précédents paragraphes le formulenement du paramètre facultatif « Valeur_proche » de la formule RECHERCHEV(), et quelles conséquences son utilisation va avoir sur le résultat retourné par notre formule.

Nous allons maintenant voir au travers d’exemple plus concret toute la puissance de ce paramètre, et qu’il n’est pas toujours nécessaire d’effectuer une recherche exacte.

Pour ce nouvel exemple, nous avons à notre disposition une table qui reprend les tarifs proposés par un éditeur pour l’édition d’un ouvrage. En formule du nombre d'unités le tarif est dégressif.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

   3.1. Editer 1000 ouvrages

 

Imaginons tout d’abord que nous ayons 1000 unités à faire éditer. Sans surprise, nous allons pouvoir utiliser ici encore notre formule RECHERCHEV() :

=RECHERCHEV(1000;$B$60:$C$63;2;VRAI)
  • 1000 : nous souhaitons tout d’abord connaître le tarif pour 1000 unités, nous venons donc saisir simplement ce nombre en premier paramètre de formule. S’agissant d’une valeur numérique, nous n’avons pas à l’encadrer par des guillemets.
  • $B$60:$C$63 : il s’agit des coordonnées de notre tableau,
  • 2 : Les tarifs se trouvent sur la seconde colonne de notre tableau,
  • VRAI : nous souhaitons qu’Excel effectue une recherche approchée

Il n’y a pas de difficulté ici, il est juste à noter que le résultat retourné sera le même que nous définissions la Valeur_proche à VRAI ou à FAUX, car la valeur_recherchée (1000 unités) se trouve déjà dans notre tableau.

Il nous reste ensuite à multiplier le résultat obtenu (9€ par unité) par le nombre d’unités (9€ * 1000 ouvrages = 9000€).

 

   3.2. Editer 10000 ouvrages

 

Maintenant, effectuons le même calcul, mais pour 10000 unités, qui ne figure pas explicitement dans le tableau.

=RECHERCHEV(10000;$B$60:$C$63;2;VRAI)

La formule à utiliser est ici toujours la même (en remplaçant simplement 1000 par 10000). Avec la Valeur_proche, la formule RECHERCHEV() sa tenter d’identifier la dernière ligne qui ne dépasse pas la valeur recherchée (donc 5000 unités).

Le coût d’édition unitaire est bien de 5€, que nous pouvons multiplier par le nombre d’unités (10000 unités), afin de d’obtenir le coût d’édition total : 50000€.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Cette valeur_proche et donc plus intéressante qu'elle ne peut y paraître au premier abord car elle permet de calculer simplement des tarifs progressifs.

Il est à noter que si nous avions défini la valeur_proche à FAUX, alors la formule RECHERCHEV() nous aurait retourné une erreur.

 

   3.3. Promo pour 100000 éditions !

 

Attention toutefois, si nous modifions légèrement notre tableau des tarifs, et que le tarif pour 1000 unités corresponde désormais au tarif pour une édition de 100000 unités, les résultats vont alors être compromis. En effet, notre table ne sera dès lors plus triée de manière croissante, et Excel va renvoyer le tarif pour 100 unités (qui est la dernière valeur avant de dépasser les 5000 unités).

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

4. Trouver les colonnes dynamiquement - Tableau 2D

 

Pour ce nouvel exemple imaginons, que nous ayons un tableau en deux dimensions avec une colonne pour chaque mois et une ligne pour chaque produit. Imaginons toujours que nous voulions connaître le nombre de ventes pour un produit et un mois donné.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Dans un premier temps, nous connaissons le produit (PRODUIT B) et le mois (Février), la formule pour obtenir le résultat est la suivante :

=RECHERCHEV("Produit B";A76:D78;3)
  • Produit B : le produit sur lequel nous effectuons notre recherche,
  • A76:D78 : les coordonnées du tableau, sans les titres de colonne,
  • 3 : Février est sur la troisème colonne du tableau

Par contre cette formule est statique, et pour modifier l’un des deux paramètres, nous seront obligé de la modifier. A chaque fois. C’est pourquoi, maintenant nous souhaitons que les deux paramètres soit saisie dans des cellules.

Pour le nom du produit, il n’y a aucun problème, il suffit de saisir les coordonnées de la cellule quo contient le nom de ce produit.

Par contre, pour obtenir le numéro de la colonne qui correspond au mois demandé, les chose sont un petit peu plus complexes, et nous allons devoir utiliser une autre formule qui est la formule EQUIV() et que nous allons voir dès à présent.

Cette formule le numéro de la cellule contenue dans une plage dont la valeur est égale à une valeur recherchée. Nous allons la saisir dans la cellule C81 :

=EQUIV(B81;B75:D75;0)
  • B81 : il s’agit de la cellule qui contient la valeur que nous recherchons (« Février »)
  • B75:D75 : il s’agit de la plage qui contient les cellules sur lesquelles nous effectuons notre recherche (les titres de colonne)
  • 0 : Permet d’indiquer que nous effectuons une recherche exacte

Ainsi, cette formule renvoie directement le numéro de la colonne (il faudra lui ajouter +1 pour tenir compte de la première colonne que nous n’avons pas considéré dans la formule EQUIV()), il nous suffit d’insérer la valeur de cette cellule dans notre formule RECHERCHEV(), qui devient alors :

=RECHERCHEV(B80;A76:D78;C81+1)

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Il suffit ensuite de changer de mois (cellule B81) ou de produits (cellule B80) pour qu'automatiquement le résultat évolue en formule des paramètres que nous spécifions.

 

 

5. Utiliser plusieurs critères de recherche

 

Voici un nouveau cas de figure nous avons toujours les mêmes données, mais non plus présentées sur un tableau en deux dimensions avec des colonnes et des lignes mais uniquement sous la forme de colonnes. Pour retrouver le résultat nous allons devoir concaténer les colonnes. Cela consiste à assembler les valeurs contenues dans plusieurs colonnes au sein d’une seule autre colonne.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Pour cela, nous ajouter une nouvelle colonne à notre tableau que nous appelons « Index ». Nous allons ensuite la remplir avec la concaténation des colonnes « Produit » et « Mois », grâce à la formule suivante (en A89, que nous étendrons ensuite à l’ensemble des cellules de la colonne) :

=B89&C89

Concaténer deux cellules consiste donc à saisir les coordonnées de chacune d’entre elle, en les « collant » grâce au symbole « & ».

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Enfin, nous allons pouvoir créer une formule RECHERCHEV() qui va effectuer la recherche sur cete nouvelle colonne « Index ». :

=RECHERCHEV(B99&B100;A89:D97;4;FAUX)
  • B99&B100 : Comme pour la création de la colonne « Index », nous allons concatener les deux champs de recherche, situés en B99 et en B100 (« Produit BJanvier » pour une recherche sur le produit B et le mois de Janvier)
  • A89:D97 : Coordonnées de la table, avec pour première colonne celle que nous venons d’ajouter (« Index »)
  • 4 : Le montant des ventes se situe sur la quatrième colonne
  • FAUX : Nous souhaitons effectuer une recherche exacte

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Attention, il est important que la colonne que nous venons d'ajouter soit la première colonne de notre table et cette dernière doit être triée par ordre alphabétique.

 

6. Recherche sur une partie de champ

 

Pour ce nouvel exemple nous avons des ventes par pays exprimées en k€. Par contre comme nous pouvons le voir ici la recherche que nous voulons effectuer n'est pas constitué d'un libellé qui corresponde exactement à celui que nous allons retrouver en première colonne du tableau.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Pour effectuer une recherche, il va donc falloir le modifier très légèrement avec une formule :

=RECHERCHEV(DROITE(A113;NBCAR(A113)-9);$B$108:$C$111;2)

La formule DROITE() permet d’extraire n caractères situés sur la droite d’une autre chaîne de caractère. En effet, si nous analysons les libellés des champs de recherche (« Pays A – France », « Pays B – Belgique », « Pays C – Italie »), on constate que le pays se trouve sur la seconde partie. Il faut donc extraire la partie de gauche, qui est constituée de 9 caractères (en comptant les espaces).

La formule DROITE() est utilisée de la manière suivante :

=DROITE(A113;NBCAR(A113)-9)
  • A113 : Coordonnées de la cellule dans laquelle se trouve l’élément recherché (« Pays A – France »)
  • NBCAR(A113)-9 : On recherche dans un premier temps le nombre de caractère contenu dans la cellule, puis on retire les 9 caractères superflus

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

7. Recherche sur deux tableaux

 

Dans ce nouveau cas de figure, les données sont dispatchées dans deux tableaux. Ainsi, nous allons devoir adapter notre formule pour chercher l’information dans le bon tableau, en formule de la présence ou non de l’élément recherché.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Si nous recherchons les USA, nous allons avoir un problème, car les USA ne se trouvent pas sur le premier tableau mais sur le second. Nous pourrions adapter à la main notre formule au fur à mesure pour aller pointer directement sur le bon tableau, mais ce ne serait pas très pratique et pas du tout dynamique.

Nous allons voir deux méthodes pour arriver à notre but, la première utilise la formule SIERREUR() que nous avons déjà étudier dans les paragraphes précédents, et la seconde utilise quatre (!) formules imbriquées : SI(NON(ESTERREUR(EQUIV()))).

 

   7.1. RECHERCHEV() et SIERREUR()

 

La formule SIERREUR() va nous permettre d’effectuer dans un premier temps une recherche exacte sur le premier tableau, puis si le résultat obtenu s’avère être une erreur, nous pourrons alors effectuer la recherche sur le second tableau.

=SIERREUR(RECHERCHEV(A128;$B$122:$C$125;2;FAUX);RECHERCHEV(A128;$E$122:$F$125;2;FAUX))

Il est important de bien définir le paramètre Valeur_proche à FAUX afin de pouvoir capter l’erreur éventuelle de la première formule RECHERCHEV().

Dans notre exemple qui consiste à chercher les ventes réalisées aux USA, Excel va nous retourner une erreur sur la première tentative, puis grâce à la formule SIERREUR() va effectuer une seconde recherche sur l’autre tableau.

 

   7.2. RECHERCHEV() et SI(NON(ESTERREUR(EQUIV())))

 

La formule que nous allons voir ici est beaucoup plus complexe, et n’a pour seul intérêt que de permettre de n’avoir à utiliser qu’une seule formule RECHERCHEV(), en déterminant de manière dynamique les coordonnées du tableau dans lequel la recherche doit être réalisée.

La formule complète est la suivante :

=RECHERCHEV(A129;SI(NON(ESTERREUR(EQUIV(A129;B122:B125;)));B122:C125;E122:F125);2;FAUX)
  • A129 : Coordonnées de la cellule dans laquelle se trouve l’élément à rechercher (« France »),
  • SI(NON(ESTERREUR(EQUIV(A129;B122:B125;)));B122:C125;E122:F125) : Récupération dynamique des coordonnées de la table dans laquelle Excel doit réaliser la recherche. Ce traitement se décompose de la manière suivante :
    • EQUIV(A129;B122:B125;) : Tout d’abord, Excel va chercher si la valeur de la cellule A129 se retrouve dans les en-têtes de ligne du premier tableau (B122:B125). Si c’est le cas, alors Excel va retourner la position de la cellule, et dans le cas contraire une erreur de type #N/A,
    • ESTERREUR() : Si le résultat retourné par la formule EQUIV(), vue juste au dessus est une erreur, alors la formule ESTERREUR() va retourner la valeur VRAI, si ce n’est pas une erreur (position de la cellule), alors cette formule retournera FAUX,
    • NON() ; Cette formule permet d’inverser les valeur VRAI et FAUX. De cette manière, si Excel retrouve effectivement la valeur de la cellule A129 dans la plage B122:B125, alors le résultat retourné sera VRAI. Il sera FAUX dans le cas contraire.
    • SI(;;) : Pour finir, si le résultat retourné par le formule NON() est vrai, alors la recherche sera effectué sur le tableau ayant pour coordonnées la plage B122:C125. Sinon elle sera sur le tableau situé en E122:F125
  • 2 : Les ventes se trouvent sur la seconde colonne de chaque tableau
  • FAUX : Nous demandons à ce que la recherche se fasse sur la valeur exacte de A129

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

De par sa complexité, nous laisseront cette solution de côté, pour lui préférer la première formule étudiée dans le point précédent.

 

8. RECHERCHEV()  et les formules matricielles

 

   8.1. Rechercher plusieurs éléments avec une seule formule

 

Nous allons maintenant voir comment utiliser la formule RECHERCHEV() avec des formules matricielles.

Contrairement aux formules classiques, une formule matricielle permet de valider plusieurs formules en même temps. De plus, elle n'est pas à valider comme n'importe quelle formule avec la touche [Entrée] mais avec la combinaison [Ctrl]+[Maj]+[Entrée].

Lorsqu’Excel identifie une formule matricielle, automatiquement des accolades vont être ajoutées au début et à la fin de la formule. Ces accolades s’ajoutent et se suppriment automatiquement et ne doivent surtout pas ajoutées à la main.

Pour cet exemple nous souhaitons récupérer d’un seul coup et avec une seule formule une série d’informations concernant un pays (par exemple l’Espagne) :

  • Le montant des ventes réalisées,
  • Les initiales du responsable national,
  • L’effectif total travaillant dans le pays

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Pour cela, nous procédons de la manière suivante :

  • Pour commencer sélectionnons les trois cellules dans lesquelles nous allons insérer les résultats,
  • Puis nous allons saisir la formule que nous allons voir juste après,
  • Et enfin nous allons valider avec la combinaison de touches [Ctrl]+[Maj]+[Entrée]

 

La formule à insérer est la suivante :

=RECHERCHEV(B143;B137:E140;{2.3.4};FAUX)
  • B143 : coordonnées de la cellule contenant le nom du pays à rechercher,
  • B137:E140 : coordonnées de la plage contenant le tableau de données (sans les titres)
  • {2.3.4} : c’est ici que nous retrouvons la particularité de la formule matricielle, les numéros de chacune des colonnes dans lesquelles se trouvent les informations (deuxième, troisième et quatrième colonne) que nous souhaitons retrouver, séparées par des points (« . ») et saisies entre accolades,
  • FAUX : nous effectuons une recherche exacte

 

L’ensemble des informations est directement mis-à-jour, il suffit de changer le pays pour modifier le résultat de l'ensemble des cellules.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

 

   8.2 Faire une somme dynamique grâce à RECHERCHEH()

 

Pour ce nouvel exemple, nous disposons nous retrouvons un tableau présentant les ventes réalisées par pays et pour chaque mois. Imaginons que nous voulions récupérer les ventes du premier trimestre de la France.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

La solution consiste à utiliser une formule matricielle en combinant la formule SOMME() et la formule RECHERCHEH() :

=SOMME(RECHERCHEH(B162;C147:E159;{2.3.4};FAUX))

(Pour rappel, une formule matricielle est à valider avec les touches [Ctrl]+[Maj]+[Entrée])

La formule matricielle RECHERCHEH() que nous venons de saisir va retourner une matrice avec l’ensemble des éléments situés aux lignes deux (le mois de janvier), trois (février) et quatre (mars). Cette formule est donc égale à :

=SOMME(48613;66064;29665)

Et enfin la formule SOMME() se charge comme son nom l’indique de sommer l’ensemble de ces résultats.

Note : Il est également possible de parvenir au même résultat en utilisant la formule SOMMEPROD() de la manière suivante (formule normale à valider avec la seule touche [Entrée] :

=SOMMEPROD(RECHERCHEH(B162;C147:E159;{2.3.4};FAUX))

 

9. Plus puissant de RECHERCHEV : INDEX(EQUIV()) !

 

Enfin pour cette dernière partie nous allons voir une alternative aux formules RECHERCHEV() et RECHERCHEH(), il s'agit du mélange de deux formules :

  • la formule INDEX(matrice;no_ligne) : affiche la valeur de la cellule situé à la ligne no_ligne dans la matrice. La valeur de no_ligne est déterminé par la formule EQUIV(),
  • la formule EQUIV(valeur_cherchée;tableau_recherche;[type]) : nous l’avons déjà abordé précédemment, cette formule permet de connaître la position de la valeur_cherchée au sein du tableau_recherche. Le paramètre optionel type permet de spécifier si l’on souhaite effectuer une recherche exacte (dans ce cas type vaut 0), ou alors la valeur la plus élevée qui est inférieure ou égale à celle de valeur_cherchée (1), ou enfin la plus petite valeur qui est supérieure ou égale à celle à valeur_cherchée (-1).

 

Pour l’exemple, nous disposons d’une table qui répertorie différentes machines-outils, avec le nombre d’unités que chacune d’elle est capable de produire chaque année.

Excel formation - formation vido gratuite tout absolument tout savoir sur la formule recherchev

Pour retrouver le nombre de pièces produites par la machine n°1119, la formule à saisir est donc la suivante :

=INDEX(D170:D174;EQUIV(1119;B170:B174;0))

Note : l’ordre des machines ne respecte pas l’ordre alphabétique, la formule RECHERCHEV() ne permettrait donc pas de retrouver à tous les coups les informations demandées.

L’autre aspect très intéressant de la formule INDEX(EQUIV()) est que l’ordre des colonnes n’a aucune importance. En effet, dans les premiers paragraphes, nous avons vu qu’il était indispensable pour le bon formulenement de la formule RECHERCHEV() que la colonne de recherche soit la première du tableau. Dans la formule INDEX(EQUIV()), il n’y a pas de connexion directe entre la colonne de recherche et celle qui contient le résultat, aussi leur ordre n’a aucune importance.

Pour illustrer cette notion, nous souhaitons connaître le numéro de la machine qui a fabriqué 2188 unités. Avec notre tableau actuel, il est impossible de retrouver cette information grâce à la formule RECHERCHEV(), il faudrait en effet inverser les colonnes. Par contre il suffit d’utiliser la formule INDEX(EQUIV()) de la manière suivante :

=INDEX(B170:B174;EQUIV(2188;D170:D174;0))

La formule INDEX(EQUIV()) possède de nombreux avantages par rapport à la formule RECHERCHEV() (ou la formule RECHERCHEH()) :

  • Le premier avantage c'est que l'ordre des colonnes dans notre tableau n'a pas d'importance. Nous venons en effet de voir que dans la formule INDEX(EQUIV()) la colonne de recherche et la colonne contenant le résultat sont indépendantes l’une de l’autre. C'est le résultat retourné par la formule EQUIV() qui va permettre de déterminer le résultat retourné par la formule INDEX(),
  • Ensuite on peut effectuer simplement des recherches verticales ou horizontales avec une seule formule, il n'y a pas besoin de faire varier la formule en fonction de la disposition de notre tableau,
  • Les colonnes n'ont pas à être triées,
  • Il n'y a pas besoin de compter nos colonnes : avec la formule RECHERCHEV(), le troisième paramètre nécessite de spécifier le numéro de la colonne qui contient les données à retourner (sauf à utiliser la formule… EQUIV() !). Cet avantage sous-entend aussi que l'on peut venir intercaler des colonnes dans notre tableau sans que cela ne viennent impacter les résultats déjà calculés

 

10. Télécharger le fichier d'exemple

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 



Articles qui pourraient vous intéresser

Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?
Comment créer un décompte entre deux dates (jours, heures, minutes et secondes) sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.