Avez-vous déjà eu besoin de récupérer une valeur dans une plage de données en fonction d’une valeur correspondante dans votre liste ? Imaginons par exemple que vous vouliez extraire de manière dynamique le prix d’un produit en fonction de son identifiant.
Si votre ensemble de données contient l’identifiant du produit, vous pouvez utiliser une formule pour en extraire le prix.
Avec cette formule définie comme formule de colonne, chaque ligne (et toute nouvelle ligne ajoutée) affichera le prix correspondant tiré de l’ensemble de données du produit.
Une formule, plusieurs approches
Vous pouvez utiliser trois méthodes pour extraire des données d’une plage en fonction d’une valeur de recherche correspondante :
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Nous verrons comment utiliser ces formules et pèserons le pour et le contre de chaque approche.
VLOOKUP
Une formule VLOOKUP recherche et renvoie une valeur dans la même ligne, mais tirée d’une colonne différente (définie). Vous trouverez ci-dessous le format d’une formule VLOOKUP :
=VLOOKUP([Valeur de recherche], [Ensemble de données concerné par la recherche], [Numéro de colonne dans l’ensemble de données],[Faux ou Vrai en fonction des besoins de correspondance exacte])
Pour extraire le prix dans l’exemple ci-dessus, votre formule VLOOKUP ressemblerait à cela :
=VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false)
La plage de références à la feuille croisée {Product Data | Product} se présente comme suit :
Et la formule renvoie les valeurs dans la colonne Price (Prix) comme suit :
ASTUCE : entourez la formule d’une fonction IFERROR pour anticiper les cas où aucune correspondance n’est trouvée dans la plage de données concernée par la recherche. Dans cet exemple, la formule ressemblerait à :
=IFERROR(VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false), "No Match Found")
Avantages :
- Formule simple/la plus rapide
Inconvénients :
- La valeur de recherche doit apparaitre dans la première colonne de l’ensemble de données concerné par la recherche
- Impossible d’extraire les valeurs situées à gauche de la colonne de valeur de recherche
- Ne fonctionne plus en cas d’ajout ou de suppression d’une colonne entre la valeur de recherche et la colonne en cours d’extraction, ou si les colonnes sont réorganisées
INDEX(MATCH())
Une formule INDEX(MATCH()) effectue une recherche sur une plage de données et extrait la valeur qui correspond aux critères spécifiés. Vous trouverez ci-dessous le format d’une formule INDEX(MATCH()) :
=INDEX([Plage où la valeur sera renvoyée],MATCH([Valeur de recherche],[Plage avec la valeur recherchée],[0, 1 ou -1 selon le type de recherche]))
Pour extraire le prix dans l’exemple ci-dessus, votre formule INDEX(MATCH()) ressemblerait à cela :
=IFERROR(INDEX({Product Data | Price}, MATCH([Associated Product ID]@row, {Product Data | Product ID}, 0)), "No Match Found")
La plage de références à la feuille croisée {Product Data | Price} se présente comme suit :
La plage de références à la feuille croisée {Product Data | Product ID} se présente comme suit :
Comme conseillé plus tôt, nous avons entouré notre fonction INDEX(MATCH()) d’une formule IFERROR pour afficher « No Match Found » (« Aucune correspondance » en français) si aucun identifiant produit correspondant n’est trouvé pour la ligne. La formule renvoie donc les valeurs dans la colonne Price (Prix) comme suit :
Avantages :
- Permet de modifier l’ordre des colonnes ou de supprimer les colonnes inutilisées sans pour autant briser la formule
- Permet d’extraire des valeurs tirées des colonnes situées à gauche ou à droite de la plage de valeurs de recherche
- Solution plus rapide pour les grands ensembles de données
- Fonction INDEX(MATCH(),MATCH()) disponible pour la mise en correspondance dynamique des colonnes et des lignes
- Nombre total de cellules référencées généralement plus faible, pour rester sous le seuil maximal des 100 000 cellules référencées pour les références à des feuilles croisées
Inconvénients :
- Nécessite plusieurs références de feuilles croisées dans les cas où les données de référence existent dans une feuille distincte
- Formule limitée à un seul critère de correspondance
INDEX(COLLECT())
Une formule INDEX(COLLECT()) effectue une recherche sur une plage de données et extrait la valeur qui correspond à un ou plusieurs critères spécifiés. Vous trouverez ci-dessous le format d’une formule INDEX(COLLECT()) :
=INDEX(COLLECT([Plage où la valeur sera renvoyée],[Plage avec le critère],[Critère],[Plage 2 avec le critère],[Critère], etc.),[1 pour l’index de ligne à renvoyer])
Pour extraire le prix dans l’exemple ci-dessus, votre formule INDEX(COLLECT()) ressemblerait à cela :
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Associated Product ID]@row), 1), "No Match Found")
La plage de références à la feuille croisée {Product Data | Price} se présente comme suit :
La plage de références à la feuille croisée {Product Data | Product ID} se présente comme suit :
Comme conseillé plus tôt, nous avons entouré notre fonction INDEX(COLLECT()) d’une formule IFERROR pour afficher « No Match Found » (« Aucune correspondance » en français) si aucun identifiant produit correspondant n’est trouvé pour la ligne. La formule renvoie donc les valeurs dans la colonne Price (Prix) comme suit :
Avantages :
- Permet de modifier l’ordre des colonnes ou de supprimer les colonnes inutilisées sans pour autant briser la formule
- Permet d’extraire des valeurs tirées des colonnes situées à gauche ou à droite de la plage de valeurs de recherche
- Généralement plus rapide que VLOOKUP, mais peut être plus lente que la fonction INDEX/MATCH
- Permet d’utiliser plusieurs critères dans une formule COLLECT à appliquer à plusieurs colonnes ou de créer des critères plus complexes.
- Vous permet de fournir la deuxième, troisième correspondances, etc., en remplaçant le « , 1 » à la fin de la formule au lieu de toujours fournir la première correspondance.
Inconvénients :
- Nécessite plusieurs références de feuilles croisées dans les cas où les données de référence existent dans une feuille distincte
- Potentiellement plus lente que la formule INDEX/MATCH, surtout si vous utilisez plusieurs critères
Besoin d’aide ?
Utilisez le modèle de manuel de formules pour trouver d’autres ressources d’assistance et consulter plus de 100 formules, y compris un glossaire de chaque fonction que vous pouvez tester en temps réel, ainsi que des exemples de formules courantes et avancées.
Découvrez comment d’autres clients Smartsheet utilisent cette fonction ou posez des questions sur votre cas précis auprès de la communauté Smartsheet.
Poser des questions à la communauté