Comment rechercher une valeur dans un tableau Excel

Choisir entre les six façons de chercher dans un tableau Excel

Avant d’écrire quoi que ce soit, demande-toi quel résultat tu attends, parce que les six méthodes ne rendent pas la même chose. Trois rapportent une valeur dans une cellule, une rend une position, une autre un compte, et la dernière un bloc de lignes.

Le tableau ci-dessus ne départage que les résultats, alors que le choix entre les trois fonctions de recherche se joue aussi sur leur écriture et sur la version d’Excel. Le comparatif de la fiche RECHERCHEX les met face à face sur ces critères, et les sections qui suivent s’en tiennent au geste.

Le fichier d’exemple porte le tableau sur lequel tout l’article travaille, douze interventions d’un dépanneur. Chaque ligne y porte un client, une ville, un type, un technicien et une durée. Sa zone de recherche occupe les colonnes I et J, où les cases jaunes reçoivent ce que tu cherches.

Retrouver une donnée dans un tableau Excel sans formule

La loupe d’Excel reste le moyen le plus rapide de savoir où se trouve une valeur, et elle ne demande pas de toucher au tableau. Le raccourci Ctrl + F ouvre la fenêtre Rechercher et remplacer, qui balaie la feuille active ou le classeur entier selon ton réglage.

Le bouton Rechercher tout fait la différence, parce qu’il montre d’un coup toutes les cellules qui portent le texte au lieu de poser le curseur sur la première. La liste en compte quatre ici, les trois lignes du Garage Petit et la case jaune où le nom a été tapé, puisque la loupe balaie la feuille entière et non le seul tableau. Un clic sur une ligne de cette liste y emmène directement, et la fenêtre reste ouverte pendant que tu navigues.

La limite de la loupe tient à ce qu’elle déplace le curseur sans rien produire. Elle ne remplit aucune cellule, elle ne se met pas à jour quand le tableau change, et il faut la relancer à chaque nouvelle question. Le filtre du ruban répond mieux dès qu’il s’agit de garder plusieurs lignes sous les yeux, puisqu’il masque tout le reste et laisse le tableau utilisable. Pour des critères plus fins, comme deux villes et un statut à la fois, le filtre avancé accepte une zone de critères à part.

Ramener une valeur dans une cellule avec RECHERCHEX ou RECHERCHEV

Rechercher une valeur dans un tableau et l’afficher ailleurs demande une formule, seule capable de se recalculer quand le tableau bouge. RECHERCHEX est la plus lisible des trois fonctions de recherche, parce qu’elle se lit comme la question qu’on pose, cherche ceci dans cette colonne et rends-moi celle-là.

Les trois premiers arguments suffisent, et le quatrième remplace à lui seul le SIERREUR qu’on enroulait autrefois autour de la formule. Un ticket absent renvoie alors le mot de ton choix au lieu d’un #N/A, ce qui laisse la colonne lisible.

Passer par RECHERCHEV sur une version plus ancienne

RECHERCHEX n’existe que dans Excel 2021 et dans Microsoft 365, donc un fichier partagé avec un collègue resté sur Excel 2016 garde RECHERCHEV. La formule change de forme, puisqu’elle prend toute la table d’un bloc et le numéro de la colonne à rapporter. Ce numéro se compte depuis la première colonne de la table, et non depuis la colonne A de la feuille.

Le FAUX de la fin exige la valeur exacte, et l’oublier bascule en correspondance approchée. Un ticket T-2047B tapé par mégarde rend alors Awa, le technicien du T-2047, sans le moindre message. Quand la table vit sur un autre onglet, son nom s’écrit devant la plage, un cas que détaille l’article sur la RECHERCHEV entre deux feuilles.

Trouver la ligne où se trouve une valeur dans le tableau

Certaines questions ne portent pas sur une valeur mais sur un rang, comme savoir à quelle ligne du tableau se trouve un ticket. EQUIV répond exactement à celle-là, en renvoyant le numéro d’ordre de la valeur dans la plage qu’on lui donne.

Ce 6 est un rang dans la plage et non un numéro de ligne de la feuille, puisque la plage commence en ligne 2. Pour obtenir la vraie adresse de la cellule, ADRESSE assemble le numéro de ligne et celui de la colonne, et =ADRESSE(EQUIV(J2;A2:A13;0)+1;1) rend $A$7.

Le zéro du troisième argument réclame la correspondance exacte, et il n’est pas facultatif dans les faits. Sans lui, EQUIV suppose la plage triée par ordre croissant, et =EQUIV(J6;B2:B13) rend 6 sur la colonne des clients, qui ne l’est pas. Ce 6 désigne la deuxième ligne du Garage Petit au lieu de la première, et rien ne le signale.

Combiner INDEX et EQUIV quand la colonne cherchée n’est pas la première

RECHERCHEV ne sait chercher que dans la première colonne de sa table, et un client rangé en colonne B lui échappe donc complètement. INDEX lève cette contrainte en travaillant sur deux plages indépendantes, celle où l’on cherche et celle d’où l’on rapporte, qu’EQUIV relie par leur rang commun.

EQUIV cherche le client dans la colonne B et donne son rang, qu’INDEX applique à la colonne A pour en tirer le ticket. Les deux plages doivent commencer à la même ligne, faute de quoi le rang désigne la mauvaise ligne sans qu’aucune erreur ne s’affiche. RECHERCHEX fait la même chose en un seul appel, avec =RECHERCHEX(J6;B2:B13;A2:A13), mais le couple INDEX et EQUIV reste le seul recours sur les versions qui ne l’ont pas.

Croiser une ligne et une colonne dans un tableau à double entrée

La feuille Barème du fichier d’exemple range les tarifs horaires par type d’intervention en lignes et par ville en colonnes. C’est la disposition habituelle d’un tableau à double entrée, et un INDEX suivi de deux EQUIV en tire la case du croisement. Le premier EQUIV donne la ligne, le second la colonne.

La plage donnée à INDEX ne couvre que les chiffres, sans les en-têtes, alors que les deux EQUIV travaillent justement sur ces en-têtes. Une plage étendue à B1:D4 décale tout d’un cran, et la même formule rend alors 52, le tarif d’un dépannage à Brest.

Savoir si une valeur existe dans un tableau ou une colonne

Chercher pour vérifier une présence n’est pas chercher pour rapporter, et une formule de recherche répond mal à cette question puisqu’elle renvoie #N/A quand elle échoue. NB.SI compte simplement les occurrences, et le compte vaut zéro quand la valeur manque.

NB.SI seul renvoie ce nombre d’occurrences, trois pour le Garage Petit, ce qui répond du même coup à la question du nombre d’interventions. Cette lecture vaut mieux qu’un SIERREUR posé autour d’une RECHERCHEV, qui masque l’absence de la valeur et, avec elle, les vraies erreurs de la formule.

ESTNUM autour d’un EQUIV donne le même verdict sous forme de VRAI ou FAUX, puisqu’EQUIV rend un nombre quand il trouve et #N/A sinon. La formule =ESTNUM(EQUIV(J6;B2:B13;0)) renvoie ainsi VRAI, et elle s’insère telle quelle dans une mise en forme conditionnelle pour colorer les lignes présentes dans une autre liste.

Cumuler deux critères pour ne garder qu’une seule ligne

Chercher avec plusieurs critères devient nécessaire dès que la valeur visée apparaît plusieurs fois dans le tableau. Le Garage Petit compte ainsi trois interventions, à trois statuts différents. RECHERCHEX accepte alors, à la place de la valeur à chercher, une condition construite sur plusieurs colonnes.

Chaque comparaison rend une colonne de VRAI et de FAUX, et leur multiplication ne laisse un 1 que là où les deux tiennent ensemble. C’est ce 1 que RECHERCHEX va chercher, d’où le premier argument qui surprend au premier regard.

La même idée sert avec SOMMEPROD quand c’est le numéro de ligne qui intéresse, par exemple pour une mise en forme conditionnelle. Sur les versions sans RECHERCHEX, la colonne auxiliaire reste la solution. Elle assemble le client et le statut par un &, et une RECHERCHEV ordinaire y retrouve ses petits.

Extraire toutes les lignes qui correspondent avec FILTRE

Les formules vues jusqu’ici ne rendent qu’une valeur, la première trouvée. Or la question des interventions du Garage Petit en appelle trois. FILTRE renvoie le bloc entier des lignes qui remplissent la condition, en-têtes exclues, et ce bloc se déverse tout seul dans les cellules voisines.

Le déversement réclame de la place libre à droite et en dessous. La moindre cellule occupée sur la zone d’arrivée renvoie #PROPAGATION! à la place du résultat. Le troisième argument, lui, évite un #CALC! quand aucune ligne ne correspond, en affichant le texte que tu lui donnes.

FILTRE s’enroule aussi dans une fonction de calcul, ce qui répond à une autre famille de questions. La formule =SOMME(FILTRE(F2:F13;B2:B13=J6)) totalise les durées du client trouvé, soit 4,5 heures pour le Garage Petit, sans passer par une colonne intermédiaire.

Pourquoi Excel ne trouve pas la valeur cherchée

Une recherche qui échoue vient rarement d’une faute de frappe, et chaque méthode a ses pièges à elle. Les quatre qui suivent expliquent la quasi-totalité des cas, en commençant par celui qui ne dit même pas son nom.

RECHERCHEV ne regarde que sa première colonne

La formule =RECHERCHEV(J6;A2:G13;1;FAUX) renvoie #N/A alors que le Garage Petit figure bien dans le tableau. Le client vit en colonne B, et la recherche ne balaie que la colonne A. Commence la table à la colonne où se trouve la valeur cherchée, ou passe à RECHERCHEX et au couple INDEX et EQUIV, qui ignorent cette contrainte.

Le numéro de colonne dépasse la largeur de la table

Un =RECHERCHEV(J2;A2:E13;8;FAUX) renvoie #REF!, la table A2:E13 n’ayant que cinq colonnes. Ce genre d’erreur apparaît surtout après une colonne supprimée, qui rétrécit la table sans que le numéro de la formule bouge.

La valeur n’est pas tout à fait la même des deux côtés

Un nombre collé depuis un export arrive souvent en texte, et un espace traîne volontiers au bout d’un libellé saisi à la main. Excel compare alors deux valeurs qu’il juge différentes, et la recherche rend #N/A sans autre indice. SUPPRESPACE et CNUM remettent la valeur cherchée dans la même forme que la colonne, et l’article sur la RECHERCHEV entre deux feuilles détaille ces réparations.

La fonction n’existe pas dans cette version d’Excel

Une formule qui affiche #NOM? signale une fonction que la version en place ne connaît pas. RECHERCHEX et FILTRE sont dans ce cas sur tout Excel antérieur à 2021, y compris sur un poste à jour ouvrant un fichier venu d’ailleurs. Quand le classeur circule entre plusieurs versions, RECHERCHEV et le couple INDEX et EQUIV restent donc le choix sûr.