Noms français, noms anglais
Dans un fichier réglé sur la France (Fichier > Paramètres > Paramètres régionaux), les fonctions ont leur nom français et les arguments se séparent par un point-virgule. Si vous trouvez un tutoriel en anglais, voici les correspondances :
| Français | Anglais | Rôle |
|---|---|---|
RECHERCHEV | VLOOKUP | Recherche verticale (dans une colonne) |
RECHERCHEH | HLOOKUP | Recherche horizontale (dans une ligne) |
RECHERCHEX | XLOOKUP | Recherche dans n’importe quel sens |
INDEX / EQUIV | INDEX / MATCH | Valeur à une position / position d’une valeur |
SI.NON.DISP / SIERREUR | IFNA / IFERROR | Remplacer une erreur |
FAUX / VRAI | FALSE / TRUE | Valeurs logiques |
Les exemples utilisent un onglet Tarifs fictif :
| A : Référence | B : Désignation | C : Catégorie | D : Prix HT (€) | |
|---|---|---|---|---|
| 2 | P-101 | Clé USB 64 Go | Accessoires | 12 |
| 3 | P-102 | Écran 27 pouces | Écrans | 189 |
| 4 | P-205 | Clavier sans fil | Accessoires | 35 |
| 5 | P-310 | Station d’accueil | Connectique | 129 |
RECHERCHEV : la recherche verticale
RECHERCHEV(clé_recherche; plage; index; [est_trié])- clé_recherche : la valeur cherchée, par exemple la référence saisie en
G2. - plage : la table, qui doit commencer par la colonne où l’on cherche. Si la clé est en colonne A, la plage commence en A.
- index : le numéro de la colonne à renvoyer, compté à partir de 1 dans la plage.
- est_trié :
FAUXpour une correspondance exacte. À toujours renseigner.
Le prix de la référence saisie en G2 (P-205) :
=RECHERCHEV(G2; Tarifs!$A$2:$D$5; 4; FAUX)
Résultat : 35. Les $ figent la plage quand vous recopiez la formule vers le bas ; avec des colonnes
entières (Tarifs!A2:D), les nouvelles références sont prises en compte automatiquement.
Le piège n° 1 : oublier FAUX
Sans quatrième argument, est_trié vaut VRAI : Sheets suppose que la première colonne est
triée et renvoie la valeur la plus proche inférieure ou égale à la clé. Cherchez une référence qui
n’existe pas, P-150, et vous n’aurez pas d’erreur : la formule renvoie 189, le prix de P-102, sans rien signaler.
Avec FAUX (ou 0), vous obtenez un #N/A honnête. Je n’utilise
VRAI que pour les barèmes par tranches, comme dans l’exemple suivant.
RECHERCHEH : la même chose à l’horizontale
RECHERCHEH(clé_recherche; plage; index; [est_trié]) cherche dans la première ligne et
renvoie la ligne numéro index. Elle sert surtout pour les grilles disposées en ligne, comme ce barème de
remise (onglet Remises) :
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quantité minimum | 1 | 10 | 50 | 100 |
| 2 | Remise | 0 % | 5 % | 10 % | 15 % |
=RECHERCHEH(C2; Remises!$B$1:$E$2; 2; VRAI)
Pour 37 unités en C2, la formule renvoie 5 % : 10 est le plus grand seuil inférieur ou égal à 37. C’est
le cas où VRAI est utile, à condition que la ligne des seuils soit triée par ordre
croissant.
RECHERCHEX : la version moderne
Disponible dans Google Sheets depuis 2022, RECHERCHEX sépare la colonne où l’on cherche de celle que l’on
renvoie :
RECHERCHEX(clé_recherche; plage_recherche; plage_résultats; [valeur_manquante]; [mode_correspondance]; [mode_recherche])=RECHERCHEX(G2; Tarifs!A2:A; Tarifs!D2:D; "Référence inconnue")Ce que cela change au quotidien :
-
Correspondance exacte par défaut (
mode_correspondance= 0) : le piège duFAUXoublié disparaît. - Valeur de repli intégrée : plus besoin d’envelopper la formule dans
SI.NON.DISP. -
Recherche à gauche : retrouver la référence à partir de la désignation, impossible avec
RECHERCHEVsans bricolage :=RECHERCHEX("Clavier sans fil"; Tarifs!B2:B; Tarifs!A2:A)renvoie P-205. -
Plusieurs colonnes d’un coup :
=RECHERCHEX(G2; Tarifs!A2:A; Tarifs!B2:D)remplit trois cellules (désignation, catégorie, prix). Les cellules à droite doivent être vides, sinon#REF!. -
Dernière occurrence : avec
mode_rechercheà-1, la recherche part du bas. Pratique pour le dernier prix pratiqué dans un historique :=RECHERCHEX(G2; Historique!B2:B; Historique!C2:C; "Aucun"; 0; -1). -
Barèmes :
mode_correspondanceà-1renvoie la valeur exacte ou, à défaut, la plus proche inférieure :=RECHERCHEX(C2; Remises!B1:E1; Remises!B2:E2; "Hors grille"; -1). -
Caractères génériques :
mode_correspondanceà2permet=RECHERCHEX("*sans fil*"; Tarifs!B2:B; Tarifs!D2:D; "Aucun"; 2).
Seule contrainte : plage_recherche et plage_résultats doivent avoir le même nombre de lignes
(A2:A avec D2:D, pas D1:D).
RECHERCHEV, RECHERCHEH, RECHERCHEX ou INDEX/EQUIV ?
| RECHERCHEV / RECHERCHEH | RECHERCHEX | INDEX + EQUIV | |
|---|---|---|---|
| Correspondance par défaut | Approximative (piège) | Exacte | Approximative pour EQUIV (mettre 0) |
| Recherche à gauche | Non | Oui | Oui |
| Colonne insérée dans la table | Mauvais résultat possible | Suit la colonne | Suit la colonne |
| Valeur si introuvable | Avec SI.NON.DISP | Intégrée | Avec SI.NON.DISP |
| Tableau à double entrée | Peu pratique | Possible, moins lisible | Idéal |
| Fichiers ouverts dans un ancien Excel | Oui | Excel 2021 / 365 seulement | Oui |
Les erreurs les plus fréquentes
#N/A : la valeur n’est pas trouvée
-
Espaces invisibles : « P-205 » et « P-205 » ne sont pas égaux. Nettoyez la source avec
SUPPRESPACE()ou le menu Données > Nettoyage des données. -
Nombre contre texte : un code client 1024 saisi comme nombre d’un côté et comme texte de l’autre ne
correspond pas. Uniformisez le format de la colonne, ou forcez la clé en texte avec
G2&"". - Plage qui ne commence pas à la bonne colonne (RECHERCHEV) : si la clé est en B, la plage commence en B.
-
La valeur n’existe vraiment pas : c’est le seul cas où il faut masquer l’erreur. Préférez
SI.NON.DISP, qui ne capture que#N/A, àSIERREUR, qui cacherait aussi une vraie faute de formule :=SI.NON.DISP(RECHERCHEV(G2; Tarifs!A2:D; 4; FAUX); "Référence inconnue").
#REF! : l’index dépasse la plage
RECHERCHEV(G2; A2:C; 4; FAUX) demande la 4e colonne d’une plage qui n’en compte que trois.
L’index se compte dans la plage, pas depuis la colonne A de la feuille.
Le piège silencieux : une colonne insérée
Quelqu’un insère une colonne « Fournisseur » entre B et C dans l’onglet Tarifs. La plage devient
A2:E, mais l’index reste 4 : toutes vos formules renvoient désormais la catégorie au lieu du prix, sans
aucune erreur. Deux parades : RECHERCHEX, qui pointe directement sur la colonne des prix, ou un index
calculé à partir de l’en-tête :
=RECHERCHEV(G2; Tarifs!A2:E; EQUIV("Prix HT (€)"; Tarifs!A1:E1; 0); FAUX)INDEX + EQUIV : quand l’utiliser ?
EQUIV(clé; plage; 0) renvoie la position d’une valeur, INDEX(plage; ligne;
[colonne]) renvoie la valeur située à une position. Combinées, elles cherchent dans n’importe quel sens :
=INDEX(Tarifs!A2:A; EQUIV("Clavier sans fil"; Tarifs!B2:B; 0))
N’oubliez pas le 0 d’EQUIV : sans lui, la recherche est approximative, comme avec
RECHERCHEV. Je garde ce duo pour deux cas : les fichiers partagés avec des utilisateurs d’Excel 2019 ou
antérieur, et les tableaux à double entrée. Avec les commerciaux en A2:A4, les mois en
B1:M1 et les objectifs dans B2:M4 :
=INDEX(B2:M4; EQUIV(G1; A2:A4; 0); EQUIV(G2; B1:M1; 0))La formule renvoie l’objectif du commercial choisi en G1 pour le mois choisi en G2.
Plusieurs critères et colonnes entières
Pour chercher sur deux critères (un client et une région), on concatène les clés de part et d’autre, avec un séparateur pour éviter les collisions. Avec un onglet Ventes où le client est en A, la région en B et le montant en E :
=ARRAYFORMULA(RECHERCHEX(G2&"|"&H2; Ventes!A2:A&"|"&Ventes!B2:B; Ventes!E2:E; "Introuvable"))
Pour remplir toute une colonne avec une seule formule plutôt que de la recopier sur des milliers de lignes, enveloppez
la recherche dans ARRAYFORMULA :
=ARRAYFORMULA(SI(A2:A=""; ""; SI.NON.DISP(RECHERCHEV(A2:A; Tarifs!A2:D; 4; FAUX); "Inconnue")))
Le fonctionnement d’ARRAYFORMULA, et de MAP quand elle ne suffit pas, est détaillé dans le
guide ARRAYFORMULA, MAP et MAKEARRAY.
Ma recommandation
- Nouveau fichier :
RECHERCHEX, systématiquement. -
Fichier existant plein de RECHERCHEV : inutile de tout réécrire ; vérifiez seulement que chaque
formule se termine par
FAUX. -
Tableau à double entrée ou compatibilité avec un ancien Excel :
INDEX+EQUIV. -
Plusieurs lignes à renvoyer (toutes les ventes d’un client) : ni l’une ni l’autre, qui ne renvoient
que la première correspondance. Passez à
FILTERou à la fonction QUERY.
FAQ
Pourquoi ma RECHERCHEV renvoie-t-elle une mauvaise valeur sans erreur ?
Presque toujours parce que le dernier argument manque ou vaut VRAI : la recherche est alors approximative.
Ajoutez FAUX. Deuxième cause : une colonne insérée qui décale l’index.
RECHERCHEV fait-elle la différence entre majuscules et minuscules ?
Non : « p-205 » trouve « P-205 ». RECHERCHEX non plus. Si la casse compte, utilisez
FILTER avec EXACT().
Peut-on faire une RECHERCHEV dans un autre fichier Google Sheets ?
Oui, en plaçant IMPORTRANGE("url_du_fichier"; "Tarifs!A2:D") à la place de la plage. La première fois,
Sheets demande d’autoriser l’accès entre les deux fichiers. Si la table est grosse, importez-la une seule fois dans un
onglet plutôt que dans chaque formule.
RECHERCHEX existe-t-elle dans Excel ?
Oui, sous le même nom dans Excel 2021 et Microsoft 365, avec des arguments très proches. Elle n’existe pas dans Excel 2019 et les versions antérieures : un fichier exporté vers ces versions affichera des erreurs.
RECHERCHEX, INDEX/EQUIV et IMPORTRANGE sont au programme du
niveau N2 « Google Sheets avancé » de la formation Google Sheets, travaillées
directement sur les fichiers de votre équipe. Si vos tables de référence vivent dans plusieurs fichiers recopiés à la main, l’étape suivante est souvent
de les automatiser. Pour les formules conditionnelles, voir aussi
la formule SI avec plusieurs conditions.