Rechercher une valeur dans une autre feuille
Recherchez une valeur dans une autre feuille Excel avec XLOOKUP, VLOOKUP ou INDEX EQUIV, avec les bonnes références de feuille et des plages figées.
Méthode 1 : XLOOKUP entre feuilles
Supposons que la feuille courante contienne un identifiant de produit en A2 et qu’une feuille nommée Products contienne les identifiants en colonne A et les prix en colonne C. Utilisez =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found"). XLOOKUP cherche dans la première plage et renvoie la valeur alignée de la seconde ; la correspondance exacte est la valeur par défaut. Elle est disponible dans Microsoft 365 et dans Excel 2021 ou version ultérieure.
Construisez la référence en saisissant la fonction, en passant à l’autre feuille puis en sélectionnant la plage : Excel insère lui-même le préfixe de feuille. Ce préfixe est le nom de la feuille suivi d’un point d’exclamation. Si le nom contient un espace ou un signe de ponctuation, entourez-le d’apostrophes, comme dans 'Product List'!$A$2:$A$100 ; une apostrophe à l’intérieur d’un nom est doublée. Les signes dollar maintiennent les plages fixes quand la formule est recopiée.
Méthode 2 : VLOOKUP et INDEX avec MATCH
Pour des classeurs qui doivent s’ouvrir dans des versions dépourvues de XLOOKUP, utilisez =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). La clé doit se trouver dans la première colonne du tableau sélectionné, 3 est la colonne à renvoyer comptée depuis le bord gauche du tableau, et FALSE demande une correspondance exacte.
=INDEX(Products!$C$2:$C$100,MATCH(A2,Products!$A$2:$A$100,0)) sépare la plage de résultats de la plage des clés et fonctionne dans toutes les versions. Les deux plages doivent commencer et finir sur des lignes alignées. L’exercice interactif ci-dessous utilise une seule feuille, car la grille d’entraînement n’en comporte qu’une ; dans Excel, ajouter le préfixe de feuille à chaque plage source est la seule différence.
Méthode 3 : tableaux Excel et références structurées
Une plage fixe comme A2:C100 exclut silencieusement la ligne 101. Si les données de recherche forment un tableau Excel nommé tblProducts, faites référence à ses colonnes : =XLOOKUP(A2,tblProducts[ID],tblProducts[Price],"Not found"). Les noms de tableaux sont valables dans tout le classeur : cela fonctionne donc depuis n’importe quelle feuille, sans préfixe de feuille, et la plage s’étend quand des lignes sont ajoutées.
INDIRECT peut construire une référence à partir de texte, par exemple =INDIRECT("'"&B1&"'!A2") pour choisir la feuille nommée en B1. Utilisez-la avec parcimonie : elle est volatile, elle cesse de pointer au bon endroit quand une feuille est renommée, parce que le nom est dans le texte, et elle est difficile à auditer. Une référence directe ou un tableau est généralement préférable.
Garder le lien en bonne santé
Gardez la feuille de recherche dans le même classeur quand c’est possible. Une référence vers un autre fichier ressemble à [Prices.xlsx]Sheet1!$A$2:$C$100 et exige que ce fichier soit accessible, avec des invites d’actualisation et de sécurité supplémentaires. Supprimer la feuille source transforme les formules en erreurs #REF! ; renommez donc les feuilles plutôt que de les recréer.
Si une recherche entre feuilles renvoie #N/A alors que la clé semble présente, la cause se trouve dans les données, pas dans la référence de feuille : voyez pourquoi une VLOOKUP renvoie #N/A à cause des espaces, des clés texte et nombre mélangées et de la correspondance approximative.
À vous de jouer
En F2, renvoyez le prix correspondant à l’identifiant en E2. Dans Excel, le tableau A:C pourrait se trouver sur une autre feuille.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Find ID | Price | |
| 2 | P-1 | Lamp | 18 | P-2 | ||
| 3 | P-2 | Chair | 24 | |||
| 4 | P-3 | Desk | 55 | |||
| 5 | P-4 | Shelf | 32 |
Réponse : =XLOOKUP(E2,A2:A5,C2:C5,"Not found")
Exemple vérifié
Les commandes contiennent les identifiants ; les prix se trouvent sur une seconde feuille nommée « Product List », d’où des apostrophes nécessaires dans toutes les références. P-9 ne figure pas dans la liste.
| A | |
|---|---|
| 1 | Product ID |
| 2 | P-2 |
| 3 | P-9 |
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | P-1 | Lamp | 18 |
| 3 | P-2 | Chair | 24 |
| 4 | P-3 | Desk | 55 |
| Formule | Résultat |
|---|---|
=XLOOKUP(A2,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | 24 |
=XLOOKUP(A3,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | "Not found" |
=VLOOKUP(A2,'Product List'!$A$2:$C$4,3,FALSE) | 24 |
=INDEX('Product List'!$B$2:$B$4,MATCH(A2,'Product List'!$A$2:$A$4,0)) | "Chair" |
=SUM('Product List'!$C$2:$C$4) | 97 |
Erreurs fréquentes
- Oublier les apostrophes autour d’un nom de feuille contenant des espaces.
- Laisser les plages sources relatives avant de recopier vers le bas.
- Utiliser une
VLOOKUPapproximative pour des identifiants ou des codes.
Questions fréquentes
Comment référencer une feuille dont le nom contient des espaces ?
Entourez son nom d’apostrophes, par exemple 'Product List'!A2:A100.
XLOOKUP peut-elle renvoyer une valeur située à gauche ?
Oui. Ses plages de recherche et de résultats sont indépendantes.
Pourquoi la référence se déplace-t-elle quand je recopie la formule ?
Verrouillez les plages de recherche avec des signes dollar ou utilisez des références structurées de tableau.
Puis-je chercher dans un autre classeur ?
Oui, mais les liaisons externes ajoutent des contraintes de chemin, d’accès, d’actualisation et de maintenance. Gardez le classeur source disponible et testez le comportement du lien.
Mettre la compétence en pratique
Poursuivez avec une leçon FormulaCamp vérifiée (en anglais) : A Lookup Table on Another Sheet (en anglais) Gratuit
Ouvrir la leçon gratuite (en anglais)