RECHERCHEV, INDEX et EQUIV : les fonctions de recherche Excel
Retrouver le nom d'un client à partir de son code, le taux de remise d'un produit à partir de sa référence, le taux d'imposition correspondant à un revenu : voilà des besoins quotidiens du gestionnaire, et des questions qui tombent presque chaque année à l'épreuve UE 8 du DCG. Savoir comment utiliser RECHERCHEV, INDEX et EQUIV dans Excel est donc une compétence incontournable, avec quelques pièges bien connus des correcteurs.
RECHERCHEV : comment utiliser la fonction de recherche la plus testée au DCG
La syntaxe est la suivante :
=RECHERCHEV(valeur_cherchée ; table ; n°_colonne ; correspondance)
La fonction cherche la valeur dans la première colonne de la table, puis retourne la valeur située dans la n-ième colonne de la même ligne. Exemple : =RECHERCHEV(A2 ; $F$1:$H$10 ; 2 ; FAUX) cherche le contenu de A2 dans la colonne F et renvoie la valeur correspondante de la colonne G (2e colonne de la table).
Deux réflexes obligatoires :
- la table de référence doit être en références absolues (
$F$1:$H$10) pour pouvoir recopier la formule ;
- le quatrième argument doit être précisé :
FAUX (ou 0) pour une correspondance exacte.
FAUX ou VRAI : correspondance exacte ou recherche par tranches
Avec FAUX, RECHERCHEV exige une correspondance exacte : c'est le mode adapté aux codes clients, matricules et références produits. Si la valeur n'existe pas, la fonction renvoie #N/A.
Avec VRAI (ou 1), RECHERCHEV cherche la plus grande valeur inférieure ou égale à la valeur cherchée. C'est le mode des recherches par tranches : barèmes d'imposition, commissions par palier, grilles tarifaires. Condition impérative : la première colonne de la table doit être triée en ordre croissant, faute de quoi les résultats sont incohérents sans aucun message d'erreur.
|
RECHERCHEV FAUX (exacte) |
RECHERCHEV VRAI (approchée) |
| Quand l'utiliser |
Code exact, matricule, référence |
Barème par tranches, grille tarifaire |
| Tri obligatoire ? |
Non |
Oui (1re colonne croissante) |
| Valeur absente |
Renvoie #N/A |
Renvoie la tranche inférieure |
Piège majeur : VRAI est la valeur par défaut. Si vous omettez le quatrième argument, la recherche est approximative, et le résultat peut être faux sans que rien ne le signale. C'est l'erreur la plus fréquente à l'examen.
Pour les tables organisées horizontalement (critères en ligne plutôt qu'en colonne), la fonction RECHERCHEH applique la même logique en cherchant dans la première ligne de la table.
INDEX et EQUIV : le duo plus flexible
RECHERCHEV a une limite structurelle : elle cherche toujours dans la première colonne de la table et ne peut renvoyer qu'une valeur située à sa droite. Le couple INDEX + EQUIV lève cette contrainte :
=INDEX(plage_résultat ; EQUIV(valeur ; plage_recherche ; 0))
EQUIV renvoie la position de la valeur cherchée dans une plage (le 0 final impose la correspondance exacte ; 1 cherche la plus grande valeur inférieure ou égale, comme RECHERCHEV VRAI) ;INDEX renvoie la valeur située à cette position dans la plage résultat.
La colonne de recherche peut être n'importe où, y compris à droite de la colonne résultat. Combiné avec deux EQUIV (un pour la ligne, un pour la colonne), INDEX permet aussi de croiser deux critères dans une table à double entrée : =INDEX(matrice ; EQUIV(valeur1 ; lignes ; 1) ; EQUIV(valeur2 ; colonnes ; 0)).
SIERREUR : protéger vos recherches
Quand la valeur cherchée n'existe pas, RECHERCHEV affiche #N/A, ce qui pollue le modèle et fausse les totaux. La parade :
=SIERREUR(RECHERCHEV(A2 ; Table ; 2 ; FAUX) ; "Non trouvé")
Si la recherche aboutit, le résultat s'affiche normalement ; sinon, le message de remplacement apparaît. La combinaison SIERREUR + RECHERCHEV est testée presque chaque année : sachez l'écrire sans hésiter.
Un exemple chiffré : les commissions de la société Sogelec
Prenons le cas de la société Sogelec, qui rémunère ses vendeurs par une commission fonction du chiffre d'affaires mensuel. Le barème, saisi en E2:F5 avec la première colonne triée en ordre croissant, est le suivant :
| CA minimum |
Taux de commission |
| 0 |
3 % |
| 10 000 |
5 % |
| 25 000 |
8 % |
| 50 000 |
12 % |
Le CA du vendeur figure en B2. La formule du taux s'écrit : =RECHERCHEV(B2 ; $E$2:$F$5 ; 2 ; VRAI).
- Si B2 = 18 000, la fonction trouve 10 000 (plus grande valeur inférieure ou égale à 18 000) et renvoie 5 %.
- Si B2 = 50 000, la correspondance est exacte : elle renvoie 12 %.
Par ailleurs, le nom du vendeur est retrouvé à partir de son code grâce à une table en G2:H20 : =SIERREUR(RECHERCHEV(C2 ; $G$2:$H$20 ; 2 ; FAUX) ; "Vendeur inconnu"). Ici le mode est FAUX car on cherche un code précis, et SIERREUR évite l'affichage de #N/A pour un code mal saisi.
Les erreurs fréquentes
- Omettre le 4e argument : la recherche devient approximative par défaut, avec des résultats faux silencieux.
- Utiliser VRAI sur une table non triée : aucun message d'erreur, mais des résultats incohérents ; c'est le piège le plus dangereux.
- Oublier les références absolues sur la table : la recopie fait glisser la table et les dernières lignes ne trouvent plus rien.
- Compter mal le numéro de colonne : il se compte à partir de la première colonne de la table, pas de la feuille.
- Vouloir chercher à droite avec RECHERCHEV : impossible ; c'est le cas d'usage d'INDEX + EQUIV.
FAQ
Quelle est la différence entre RECHERCHEV avec FAUX et avec VRAI ?
FAUX impose une correspondance exacte : si la valeur n'existe pas dans la première colonne, la fonction renvoie #N/A. VRAI effectue une recherche approchée : elle retient la plus grande valeur inférieure ou égale à la valeur cherchée, ce qui convient aux barèmes par tranches, à condition que la première colonne soit triée en ordre croissant.
Quand préférer INDEX + EQUIV à RECHERCHEV ?
Dès que la colonne de recherche n'est pas la première colonne de la table, ou que la valeur à renvoyer se trouve à gauche de la colonne de recherche. INDEX + EQUIV est aussi le bon choix pour croiser deux critères dans une table à double entrée (ligne et colonne).
Comment éviter l'affichage #N/A quand la valeur n'existe pas ?
En enveloppant la recherche dans SIERREUR : =SIERREUR(RECHERCHEV(...) ; "Non trouvé"). On peut aussi tester la cellule d'entrée avec ESTVIDE pour ne lancer la recherche que si un code a été saisi : =SI(ESTVIDE(A2) ; "" ; RECHERCHEV(A2 ; Table ; 2 ; FAUX)).
Entraînez-vous
Une table « Tarifs » occupe la plage F1:H10 : colonne F = code client, colonne G = nom, colonne H = taux de remise. En A2, l'utilisateur saisit un code client. 1) Écrivez la formule affichant le nom du client. 2) Écrivez la formule affichant son taux de remise. 3) Protégez la formule du nom contre les codes inexistants. 4) Un barème de commission par tranches de CA est saisi en J2:K5 (colonne J triée en ordre croissant) ; le CA est en B2 : écrivez la formule du taux de commission.
Afficher le corrigé
Nom du client : =RECHERCHEV(A2 ; $F$1:$H$10 ; 2 ; FAUX). Le code est cherché dans la première colonne de la table (F) ; le nom est en 2e colonne ; FAUX car on cherche un code exact. La table est en absolu pour permettre la recopie.
Taux de remise : =RECHERCHEV(A2 ; $F$1:$H$10 ; 3 ; FAUX). Même logique, mais on renvoie la 3e colonne de la table (H).
Protection : =SIERREUR(RECHERCHEV(A2 ; $F$1:$H$10 ; 2 ; FAUX) ; "Client inconnu"). Si le code n'existe pas, le message « Client inconnu » remplace #N/A.
Barème par tranches : =RECHERCHEV(B2 ; $J$2:$K$5 ; 2 ; VRAI). Le mode VRAI retient la plus grande borne inférieure ou égale au CA ; il exige que la colonne J soit triée en ordre croissant. Une variante équivalente avec INDEX + EQUIV : =INDEX($K$2:$K$5 ; EQUIV(B2 ; $J$2:$J$5 ; 1)).