Excel : RECHERCHEX, INDEX-EQUIV et RECHERCHEV pour retrouver une donnée (plan comptable, clients, tarifs)

Retrouver une information dans un tableau à partir d’une clé est le geste le plus fréquent du comptable sur Excel : l’intitulé d’un compte à partir de son numéro, le taux de TVA d’un produit, la raison sociale d’un client à partir de son ICE, le barème à appliquer selon un montant. Trois méthodes existent. Voici comment choisir et les utiliser.

Le cas : habiller une balance

Une balance brute n’affiche que des numéros de compte. On souhaite ajouter l’intitulé, tiré du plan comptable dans les colonnes F et G.

RECHERCHEX, la méthode moderne

Syntaxe :

=RECHERCHEX(valeur_cherchée; plage_où_chercher; plage_résultat; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])

Dans la cellule B2 de la balance :

=RECHERCHEX(A2;$F$2:$F$6;$G$2:$G$6;"Compte inconnu")

Figure 1 : le repère 1 montre la formule, le repère 2 le plan comptable (clés en F, intitulés en G), le repère 3 le cas d’un compte absent du plan.

Lecture : « cherche la valeur de A2 dans F2:F6 et renvoie la valeur située sur la même ligne dans G2:G6 ; si elle n’existe pas, écris Compte inconnu ». Recopiez vers le bas. Grâce aux $, les plages restent fixes.

Atouts :

• pas besoin de compter les colonnes ;
• la colonne de recherche peut être à droite de la colonne de résultat ;
• la valeur « non trouvé » évite les #N/A ;
• le mode de recherche permet de chercher en partant du bas (dernière occurrence).

RECHERCHEV, l’ancienne méthode

=RECHERCHEV(A2;$F$2:$G$6;2;FAUX)

Les quatre arguments : valeur cherchée, plage du tableau (la clé doit être en première colonne), numéro de la colonne à renvoyer (ici 2), FAUX pour une correspondance exacte. Limites : la clé doit être à gauche ; le numéro de colonne devient faux si l’on insère une colonne ; l’oubli de FAUX provoque des résultats faux silencieux.

INDEX + EQUIV, la méthode universelle

Pour toutes les versions d’Excel :

=SIERREUR(INDEX($G$2:$G$6;EQUIV(A2;$F$2:$F$6;0));"Compte inconnu")

• EQUIV(A2;$F$2:$F$6;0) renvoie la position de A2 dans la colonne clé (0 = exacte).
• INDEX($G$2:$G$6;position) renvoie la valeur de la colonne résultat à cette position.
• SIERREUR remplace l’erreur par un texte.

Quel outil choisir ?

Recommandation : RECHERCHEX quand elle est disponible ; sinon INDEX-EQUIV. Évitez RECHERCHEV pour tout nouveau fichier.

La recherche approchée : trouver la tranche

Autre usage : classer un montant dans une tranche. Pour la balance âgée, une table des tranches de retard :

Pour un retard de 75 jours en D2 :

=RECHERCHEX(D2;$H$2:$H$6;$I$2:$I$6;"";-1)

Le mode de correspondance -1 renvoie la valeur exacte ou la plus petite valeur immédiatement inférieure (ici 61 → « 61-90 j »). Avec INDEX-EQUIV : =INDEX($I$2:$I$6;EQUIV(D2;$H$2:$H$6;1)) (la colonne clé doit être triée par ordre croissant).

Même principe pour appliquer un barème d’IR (voir calcul de l’IR), une grille de commissions ou des taux d’amortissement.

Chercher sur plusieurs colonnes à la fois

Pour retrouver la TVA par produit et par taux ou un solde par compte et par période, on crée une clé composite : en colonne de données =A2&"|"&B2, et on cherche =RECHERCHEX(F2&"|"&G2;Clés;Valeurs). Pour un résultat numérique, SOMME.SI.ENS est plus simple (voir le tutoriel).

Éviter les pièges

1. Formats différents : le compte 5141 en nombre dans la balance et en texte dans le plan donne #N/A. Uniformisez avec =TEXTE(A2;"0").
2. Espaces invisibles : appliquez SUPPRESPACE() sur les colonnes importées.
3. Doublons dans la table de référence : une clé doit être unique.
4. Plages non figées : oublier les $ fait glisser la plage.
5. Tri requis pour les recherches approchées avec EQUIV (type 1).
6. Lignes ajoutées à la table de référence : utilisez un tableau Excel (Ctrl+T) pour que les plages s’étendent automatiquement.

Les options de RECHERCHEX à connaître

La syntaxe complète est :

=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])

Exemples d’usage en comptabilité

La dernière formule renvoie plusieurs cellules en une seule saisie (les résultats « débordent » à droite) : plus besoin de recopier une formule par colonne.

Cas pratique : relancer la dernière facture de chaque client

Un tableau de ventes trié par date contient, pour le client « Atlas », trois factures : 10/01, 14/02 et 03/03. La formule =RECHERCHEX("Atlas";Ventes[Client];Ventes[Date];"";0;-1) renvoie 03/03 (la dernière), alors que le mode par défaut renverrait 10/01. Associée à =AUJOURDHUI()-date, elle donne l’ancienneté de la dernière facture et permet de repérer les clients inactifs.

Quand garder INDEX + EQUIV

RECHERCHEX n’existe que dans Excel 2021, Excel pour Microsoft 365 et les versions récentes en ligne. Pour un fichier partagé avec des utilisateurs sur des versions plus anciennes, INDEX + EQUIV reste la solution compatible :

=INDEX(Plan[Intitulé];EQUIV(A2;Plan[Compte];0))

Choisissez en fonction de la version de vos destinataires, et documentez la formule dans une cellule de commentaire du classeur.

Cas pratique : calculer l’IR avec une table de barème

Le barème de l’IR se prête parfaitement à une recherche approchée. On crée une table avec, pour chaque tranche, le seuil bas, le taux et la somme à déduire (voir le calcul de l’IR).

Si le revenu net imposable annuel est en cellule B2 et la table nommée « Barème » (colonnes Seuil, Taux, Déduction), l’impôt s’écrit :

=B2*RECHERCHEX(B2;Barème[Seuil];Barème[Taux];0;-1)-RECHERCHEX(B2;Barème[Seuil];Barème[Déduction];0;-1)

Pour B2 = 102 520 : le seuil retenu est 100 000,01 (valeur immédiatement inférieure), le taux 34 %, la déduction 22 000, soit 102 520 × 34 % − 22 000 = 12 856,80 MAD, exactement le résultat du calcul détaillé. Le même modèle s’applique à une grille de commissions, à une échelle de remises ou à une table de taux d’amortissement. L’avantage : quand le barème change, on met à jour la table, pas les formules.

Construire une balance âgée pas à pas

1. Préparer les données : une colonne par facture (client, numéro, date d’échéance, montant). Convertissez la plage en tableau (Ctrl+T) et nommez-le « Créances ».
2. Calculer le retard : =SI(AUJOURDHUI()>[@Échéance];AUJOURDHUI()-[@Échéance];0).
3. Créer la table des tranches (voir plus haut) et nommez-la « Tranches ».
4. Attribuer la tranche : =RECHERCHEX([@Retard];Tranches[Début];Tranches[Libellé];"";-1).
5. Synthétiser : un tableau croisé dynamique avec les clients en lignes, les tranches en colonnes et la somme des montants en valeurs.
6. Contrôler : le total du tableau doit égaler le solde du compte clients du grand livre.

Résultat attendu : pour chaque client, le montant non échu, de 1 à 30 jours, de 31 à 60 jours, de 61 à 90 jours et au-delà. Cette balance est l’outil de base de la relance et de l’estimation des provisions pour créances douteuses (voir l’article sur les créances douteuses).

Les erreurs qui reviennent le plus souvent

Un réflexe de contrôle : ajoutez une cellule =NB.SI(colonne_résultat;"Compte inconnu") qui compte les clés introuvables. Un résultat différent de zéro doit être traité avant de continuer.

Une alternative sans formule : Power Query

Pour fusionner un grand livre de 100 000 lignes avec un plan comptable, des milliers de formules ralentissent le classeur. Power Query offre une solution : Données > Obtenir des données > Combiner les requêtes > Fusionner. On choisit les deux tables, la colonne clé commune, le type de jointure (gauche externe pour conserver toutes les lignes du grand livre) et on développe les colonnes voulues. Le résultat se met à jour en un clic (Actualiser tout). C’est la méthode recommandée pour les balances mensuelles produites par le même logiciel : on construit la requête une fois et on la réutilise chaque mois.

Votre check-list avant de livrer un classeur

• Les tables de référence sont des tableaux Excel, nommés.
• Chaque recherche indique un résultat « si non trouvé » explicite.
• Une cellule de contrôle compte les clés non trouvées.
• Les formats des clés sont homogènes (texte ou nombre).
• Les doublons de la table de référence ont été supprimés.
• Les totaux sont rapprochés d’une source indépendante (balance, grand livre).
• La version d’Excel des destinataires est compatible avec la fonction utilisée.

Aller plus loin

Dans Excel 365, XLOOKUP correspond à RECHERCHEX en anglais, et FILTRE permet de renvoyer toutes les lignes qui correspondent à un critère. Pour les données volumineuses, Power Query (fusion de requêtes) remplace avantageusement des milliers de formules de recherche.

Cas particuliers et situations limites

La clé comporte des majuscules et minuscules. RECHERCHEX ne fait pas de différence de casse par défaut ; pour une recherche sensible à la casse, utiliser EXACT dans une formule matricielle ou nettoyer les clés avec MAJUSCULE.

Plusieurs lignes correspondent à la même clé. Seule la première (ou la dernière avec le mode de recherche −1) est renvoyée. Pour toutes les correspondances, utiliser FILTRE ou un tableau croisé dynamique.

La table de référence est sur un autre fichier. Les liaisons externes fonctionnent, mais se cassent si le fichier est déplacé. Préférer importer la table avec Power Query ou la copier dans le classeur de travail.

Le résultat attendu est un nombre mais la cellule renvoie du texte. Entourer la formule de CNUM ou changer le format de la colonne résultat ; vérifier ensuite par une somme de contrôle.

Le classeur doit fonctionner sous une version ancienne d’Excel. Remplacer RECHERCHEX par INDEX et EQUIV : la formule reste lisible et compatible avec toutes les versions.

À retenir

RECHERCHEX est la fonction de recherche moderne : clé à gauche ou à droite, valeur par défaut, recherche du dernier élément, correspondance approchée. INDEX et EQUIV restent la solution compatible avec toutes les versions. RECHERCHEV est à éviter dans les nouveaux fichiers. Dans tous les cas, contrôler les clés non trouvées.

En résumé

Une table de référence propre (clés uniques, formats homogènes), une fonction de recherche adaptée à la version d’Excel, une valeur par défaut explicite et une cellule de contrôle comptant les clés introuvables : quatre habitudes pour des classeurs fiables.