Étiquette : INDEX et EQUIV

RechercheV avec plusieurs critères, sur un plateau

RechercheV avec plusieurs critères, sur un plateau

La fonction RECHERCHEV() est utilisée pour récupérer les données complémentaires qui se trouvent dans un second tableau, sur la base d’un unique critère (une référence ou un code d’identification). Et si vous devez vous baser sur plusieurs critères ? Comment réaliser cette opération avec RECHERCHEV() ? Est-ce la meilleure fonction pour réaliser cette opération ?

Bref rappel du fonctionnement de la RECHERCHEV() avec un seul critère

Les conditions de départ sont les suivantes pour utiliser la fonction RECHERCHEV() :

  1. Vous devez disposer d’un tableau “de référence” (vos articles, vos fournisseurs, etc.).
  2. Vos données (articles, fournisseurs, etc.) contiennent un identifiant unique. Avec cette information, n’importe qui saurait de quelle ligne de données vous parlez.
  3. Votre identifiant est positionné dans les colonnes PRÉCÉDANT les données détaillées.
  4. Vous disposez d’un second tableau où cet identifiant est utilisé.

Exemple : votre objectif est de récupérer la description détaillée d’un article à partir de son code.

Tableau 1

Code articleDescription
1Chaise
2Table
3Bureau
Table de référence

Tableau 2

N°FactureCode articleDescription
11
12
22
33
Table où vous devez utiliser RECHERCHEV()

Ici, vous souhaitez compléter automatiquement le texte de la description à l’aide de la fonction RECHERCHEV().

Celle-ci s’utilise ainsi :

RECHERCHEV(
valeur recherchée – ici, c’est le code article, à partir du tableau des factures ;
table où rechercher – attention, bien fixer la plage de recherche avec les $ ou utiliser les tableaux nommés ;
numéro de la colonne à retourner – cela veut dire que le tableau où rechercher doit inclure la donnée à retourner !! ;
acceptez-vous les valeurs approximatives – par défaut, la valeur est à VRAI !, donc si vous ne renseignez pas FAUX, vous aurez des surprises…).

Comme vous pouvez le voir, cette fonction ne semble pas laisser de place à l’utilisation de plusieurs critères…

Pour une RECHERCHEV() avec plusieurs critères, une seule solution : Abattre les limites de la RECHERCHV() !

RechercheV vers la gauche

Mon commentaire est peut-être passé inaperçu dans mon rappel de l’utilisation de la RECHERCHEV(), il est pourtant essentiel. La valeur recherchée doit se trouver dans les colonnes PRÉCÉDANT celles du tableau de recherche. Sinon, cela ne fonctionne pas…

Ainsi, si votre tableau de référence ressemble à ceci, les conditions d’utilisation de RECHERCHEV() ne sont pas respectées :

Tableau 1

DescriptionCode article
Chaise1
Table2
Bureau3
Tableau de référence

Heureusement, d’autres fonctions existent pour cela !

RECHERCHEV à l’envers pour Excel 365

Microsoft a ajouté une nouvelle fonction bien plus puissante : RECHERCHEX(). Cette fonction regroupe à elle seule RECHERCEV(), RECHERCHEH() et l’ancien RECHERCHE()… en mieux !

Je ne m’attarderai pas sur toutes les possibilités de cette nouvelle fonction ici, mais uniquement sur celle qui fait que la recherche est souvent nommée “à l’envers” car elle peut aller vers la gauche !

Au lieu de renseigner le numéro de la colonne à retourner, vous renseignez ici directement la colonne à retourner ! Où qu’elle soit…

=RECHERCHEX (
valeur recherchée – Ici, rien ne change ;
tableau où rechercher – Plus besoin de tout le tableau, uniquement la colonne où se trouve la valeur recherchée ;
tableau à retourner – Et au lieu du n° de la colonne, vous indiquez la colonne entière où se trouve la donnée à retourner. Et elle peut se trouver avant, après c’est vous qui voyez !).

Cette nouvelle fonction résout donc le problème de la recherche vers la gauche. Mais elle ne permet toujours pas d’avoir une RechercheV avec plusieurs critères…

C’est pourquoi, vous devez continuer la lecture de cet article !

RECHERCHEV à l’envers pour toutes les versions d’Excel

Ce n’est pas une fonction que vous aurez à utiliser ici, mais deux ! Et chacune d’entre elles prise indépendamment a l’apparence d’une fonction presque simpliste… alors que leur potentiel va bien plus loin et leur combinaison en fait un outil très puissant !

Ces deux fonctions sont INDEX() et EQUIV() – pour les versions anglaises, elles portent le nom de INDEX() et MATCH().

INDEX() sert à retourner le contenu de la cellule d’un tableau dont vous avez fourni les coordonnées.

INDEX(
Tableau – En général, c’est une colonne, mais cela peut être un tableau ;
numéro de ligne – Vous fournissez le numéro de la ligne où se trouve votre cellule ;
numéro de colonne – Cette valeur est optionnelle et fixée à 1 par défaut car on l’utilise souvent pour une seule colonne de données.

A priori, rien de transcendant… vous donnez les coordonnées de la cellule et Excel vous la retourne. Bon…

La deuxième fonction est EQUIV(). Celle-ci renvoie le numéro de la ligne où se trouve une valeur que vous indiquez.

EQUIV(
Valeur recherchée;
Tableau où se trouve la valeur recherchée – En général, c’est une colonne d’un tableau;
Type de recherche – Ici, on recherche l’égalité, ce sera donc 0.

Et lorsque l’on combine les deux, on peut alors faire de très belles choses ! Voyons de plus près :

INDEX renvoie le contenu d’une cellule à partir d’un tableau (en général une colonne de valeurs) et EQUIV renvoie le numéro de ligne à partir d’une colonne de valeurs (NOTA – je simplifie volontairement…).

De plus, chacune de ces deux fonctions possède SON TABLEAU dans les critères. Ainsi, EQUIV va donner un rang dans une colonne et INDEX va retourner ce rang d’une AUTRE colonne (puisque nous avons le choix des paramètres !).

Exemple d’utilisation de INDEX et EQUIV en vidéo

RechercheV avec plusieurs critères : L’équation impossible ?

Pour atteindre cet objectif, une autre étape intermédiaire doit être expliquée : les calculs matriciels (une introduction seulement…).

C’est ainsi… Exceller avec Excel passe par une meilleure compréhension de certains calculs mathématiques. En particulier, certaines notions de calculs matriciels.

Initiation aux opérations matricielles dans Excel

L’objectif n’est pas de devenir mathématicien. Juste manier Excel !

Si vous êtes l’heureux(-se) possesseur(-e) d’une licence Excel 365, vous pouvez directement tester ces fonctions dans Excel, pour les autres, vous devrez saisir ces mêmes fonctions en pressant “Ctrl + Shift + Enter”…

RechercheV Plusieurs criteres - Initiation calcul matriciel
RechercheV plusieurs critères – Initiation calcul matriciel

Au calcul =(B2:B4=F2), Excel va construire en mémoire “le tableau / la matrice” résultat.

Petite astuce ici… en ajoutant deux fois le symbole “-” devant, Excel va inverser deux fois le résultat et convertir l’affichage en valeur numérique :

RechercheV Plusieurs criteres - Calcul matriciel - Obtenir des uns et zeros
RechercheV Plusieurs critères – Calcul matriciel – Obtenir des 1 et des 0

Ce résultat indique que la valeur recherchée se trouve à la première position de notre tableau.

Produit matriciel : La lumière au bout du tunnel de notre RechercheV avec plusieurs critères

Maintenant, passons à l’étape avec deux critères sur deux colonnes différentes. J’ai ajouté une notion de collection, et notre objectif est d’identifier quel prix doit s’appliquer dans le tableau des factures.

De la même manière que précédemment, les matrices d’égalité (–(B2:B7=G2)) et (–(C2:C7=I2)) permettent d’identifier où se trouvent les cellules avec la bonne valeur.

L’astuce consiste à multiplier ces deux “matrices/tableau” entre elles. Excel va alors multiplier le premier élément avec le premier, le second avec le second, etc…

Finalement, une seule cellule du tableau résultat contient la valeur 1. Cela signifie que c’est la première ligne qui répond aux deux critères !

RechercheV Plusieurs criteres - Produit matriciel
RechercheV Plusieurs critères – Produit matriciel

Assemblage des pièces du puzzle : La RechercheV avec plusieurs critères se révèle à travers INDEX et EQUIV + les produits matriciels !

Rappelez-vous précédemment : nous avons vu que INDEX renvoie le contenu d’une cellule, lorsqu’on lui dit laquelle. Et EQUIV nous indique où se trouve une valeur dans un tableau. Enfin, le produit matriciel des “égalités” indique à l’aide de la valeur 1 à quelle ligne se trouve la cellule qui répond à tous les critères.

En combinant le tout, cela donne :

=INDEX(
$D$2:$D$7; – La colonne contenant les prix.
EQUIV(
1; – Comme vu précédemment, c’est la ligne où se trouve 1 qui donnera le résultat
(–($B$2:$B$7=G2))*(–($C$2:$C$7=I2)))) – Le fameux produit matriciel.

L’article en vidéo

Pour développer cet article, je vous propose un autre exemple illustré à l’aide de cette vidéo.

Votre avis sur cet article

Avez-vous réussi à mettre en pratique cette “RECHERCHEV avec plusieurs critères” ? Avez-vous des zones d’ombres ou des difficultés à la mettre en pratique ?

Votre avis compte et votre feedback m’est très utile ! Utilisez la zone de commentaires pour cela !

>