Maîtriser la Recherche de Valeur dans Excel : Guide Complet
Excel offre une panoplie de fonctions pour effectuer des recherches de valeurs, chacune ayant ses propres forces et faiblesses. Comprendre ces fonctions et savoir quand les utiliser est crucial pour optimiser votre travail et éviter les erreurs.
Les Bases de la Recherche de Valeur
Avant de plonger dans les formules spécifiques, il est important de comprendre les concepts de base de la recherche de valeur dans Excel. La recherche de valeur consiste à trouver une information spécifique dans une plage de cellules en se basant sur une valeur de recherche (le critère).
Les éléments clés d'une recherche de valeur sont :
- La valeur de recherche : L'élément que vous cherchez (par exemple, un nom, un code produit, un numéro de téléphone).
- La plage de recherche : La zone de la feuille de calcul où vous allez chercher la valeur de recherche.
- La colonne ou ligne de résultat : L'endroit où se trouve l'information que vous voulez récupérer (par exemple, le prix correspondant au code produit).
- Le type de correspondance : Indique si vous voulez une correspondance exacte ou approximative.
La Fonction RECHERCHEV (VLOOKUP) : La Plus Populaire
La fonction RECHERCHEV est sans doute la fonction de recherche de valeur la plus utilisée dans Excel. Elle permet de rechercher une valeur dans la première colonne d'une plage de cellules et de renvoyer une valeur correspondante dans une autre colonne de la même ligne.
Syntaxe de RECHERCHEV :
=RECHERCHEV(valeur_recherchée; table_matrice; no_index_col; [valeur_proche])
- valeur_recherchée : La valeur à rechercher.
- table_matrice : La plage de cellules dans laquelle effectuer la recherche. La première colonne de cette plage doit contenir la valeur_recherchée.
- no_index_col : Le numéro de la colonne dans
table_matricequi contient la valeur à renvoyer (la première colonne est la colonne 1). - [valeur_proche] : (Facultatif) Indique si vous voulez une correspondance exacte (FAUX ou 0) ou approximative (VRAI ou 1). Si omis, la valeur par défaut est VRAI.
Exemple Pratique :
Imaginez que vous avez une liste de produits avec leurs prix dans une feuille Excel. Vous voulez trouver le prix du produit portant le code "ABC-123".
| Code Produit | Nom Produit | Prix |
|---|---|---|
| ABC-123 | Produit A | 19.99 |
| DEF-456 | Produit B | 29.99 |
| GHI-789 | Produit C | 39.99 |
La formule RECHERCHEV serait la suivante :
=RECHERCHEV("ABC-123"; A1:C3; 3; FAUX)
"ABC-123"est la valeur à rechercher.A1:C3est la plage de recherche.3est le numéro de la colonne contenant le prix.FAUXindique que nous voulons une correspondance exacte.
Cette formule renverra la valeur 19.99.
Conseils pour RECHERCHEV :
- Assurez-vous que la
valeur_recherchéese trouve bien dans la première colonne de latable_matrice. - Utilisez
FAUXpour une correspondance exacte, surtout si vous cherchez des codes, des identifiants ou des noms. - Si
RECHERCHEVrenvoie#N/A, cela signifie que lavaleur_recherchéen'a pas été trouvée dans latable_matriceavec le type de correspondance spécifié. RECHERCHEVs'arrête à la première correspondance. Si vous avez plusieurs occurrences de lavaleur_recherchée, elle ne renverra que la valeur de la première occurrence.
La Fonction RECHERCHEH (HLOOKUP) : La Recherche Horizontale
La fonction RECHERCHEH est similaire à RECHERCHEV, mais elle effectue une recherche horizontale au lieu de verticale. Elle recherche une valeur dans la première ligne d'une plage de cellules et renvoie une valeur correspondante dans une autre ligne de la même colonne.
Syntaxe de RECHERCHEH :
=RECHERCHEH(valeur_recherchée; table_matrice; no_index_ligne; [valeur_proche])
- valeur_recherchée : La valeur à rechercher.
- table_matrice : La plage de cellules dans laquelle effectuer la recherche. La première ligne de cette plage doit contenir la valeur_recherchée.
- no_index_ligne : Le numéro de la ligne dans
table_matricequi contient la valeur à renvoyer (la première ligne est la ligne 1). - [valeur_proche] : (Facultatif) Indique si vous voulez une correspondance exacte (FAUX ou 0) ou approximative (VRAI ou 1). Si omis, la valeur par défaut est VRAI.
Exemple Pratique :
Imaginez que vous avez un tableau de données où les mois sont en première ligne et les ventes par produit sont dans les lignes suivantes.
| Janvier | Février | Mars | |
|---|---|---|---|
| Produit A | 100 | 120 | 150 |
| Produit B | 200 | 220 | 250 |
| Produit C | 300 | 320 | 350 |
Vous voulez trouver les ventes du Produit B en Février. La formule RECHERCHEH serait la suivante :
=RECHERCHEH("Février"; A1:D3; 3; FAUX)
"Février"est la valeur à rechercher.A1:D3est la plage de recherche.3est le numéro de la ligne contenant les ventes du Produit B.FAUXindique que nous voulons une correspondance exacte.
Cette formule renverra la valeur 220.
Quand utiliser RECHERCHEH ?
RECHERCHEH est utile lorsque vos données sont organisées horizontalement, avec les en-têtes de colonnes en première ligne.
La Puissance Combinée de INDEX et EQUIV
Bien que RECHERCHEV et RECHERCHEH soient pratiques, elles ont des limitations. Elles ne peuvent rechercher que dans la première colonne ou ligne d'une plage et peuvent être difficiles à utiliser dans des situations plus complexes. C'est là que la combinaison des fonctions INDEX et EQUIV entre en jeu.
La Fonction INDEX :
La fonction INDEX renvoie la valeur d'une cellule dans une plage en spécifiant son numéro de ligne et de colonne.
Syntaxe de INDEX :
=INDEX(matrice; no_ligne; [no_colonne])
- matrice : La plage de cellules dans laquelle rechercher.
- no_ligne : Le numéro de la ligne à partir de laquelle renvoyer une valeur.
- [no_colonne] : (Facultatif) Le numéro de la colonne à partir de laquelle renvoyer une valeur. Si omis,
INDEXrenvoie toute la ligne.
La Fonction EQUIV :
La fonction EQUIV recherche une valeur dans une plage et renvoie sa position relative (son numéro) dans cette plage.
Syntaxe de EQUIV :
=EQUIV(valeur_recherchée; plage_recherche; [type_correspondance])
- valeur_recherchée : La valeur à rechercher.
- plage_recherche : La plage de cellules dans laquelle effectuer la recherche.
- [type_correspondance] : (Facultatif) Indique le type de correspondance : 1 (inférieur à), 0 (exacte), -1 (supérieur à). Si omis, la valeur par défaut est 1.
Combiner INDEX et EQUIV :
La magie opère lorsque vous combinez INDEX et EQUIV. EQUIV trouve la position de la valeur de recherche, et INDEX utilise cette position pour renvoyer la valeur correspondante dans une autre colonne ou ligne.
Exemple Pratique :
Reprenons l'exemple de la liste de produits avec leurs prix :
| Code Produit | Nom Produit | Prix |
|---|---|---|
| ABC-123 | Produit A | 19.99 |
| DEF-456 | Produit B | 29.99 |
| GHI-789 | Produit C | 39.99 |
Pour trouver le prix du produit portant le code "DEF-456", la formule combinée INDEX et EQUIV serait la suivante :
=INDEX(C1:C3; EQUIV("DEF-456"; A1:A3; 0))
C1:C3est la plage contenant les prix (la colonne de résultat).EQUIV("DEF-456"; A1:A3; 0)renvoie la position de "DEF-456" dans la colonne A (qui est 2).INDEX(C1:C3; 2)renvoie la valeur de la deuxième cellule dans la plage C1:C3, qui est29.99.
Avantages de INDEX EQUIV :
- Flexibilité : Vous pouvez rechercher dans n'importe quelle colonne ou ligne, pas seulement la première.
- Performance : Dans certains cas,
INDEX EQUIVpeut être plus rapide queRECHERCHEV, surtout avec de grandes quantités de données. - Moins de risques d'erreurs :
INDEX EQUIVest moins sensible aux insertions ou suppressions de colonnes, ce qui peut casser les formulesRECHERCHEV.
Erreurs Courantes et Comment les Éviter
#N/A: Cette erreur indique que la valeur de recherche n'a pas été trouvée. Vérifiez l'orthographe de la valeur de recherche, la plage de recherche et le type de correspondance.#REF!: Cette erreur indique qu'une référence de cellule est invalide. Vérifiez que les plages de cellules dans votre formule sont correctes et qu'elles n'ont pas été supprimées ou déplacées.- Correspondance Incorrecte : Assurez-vous d'utiliser le bon type de correspondance (exacte ou approximative) en fonction de vos besoins. L'utilisation d'une correspondance approximative lorsque vous avez besoin d'une correspondance exacte peut entraîner des résultats inattendus.
- Données Non Triées (pour les correspondances approximatives) : Si vous utilisez une correspondance approximative avec
RECHERCHEVouRECHERCHEH, assurez-vous que la première colonne (pourRECHERCHEV) ou la première ligne (pourRECHERCHEH) de la plage de recherche est triée en ordre croissant.
Autres Fonctions Utiles pour la Recherche de Valeur
- CHERCHE/TROUVE : Ces fonctions permettent de rechercher une chaîne de caractères dans une autre chaîne de caractères.
CHERCHEn'est pas sensible à la casse, tandis queTROUVEl'est. - SIERREUR : Cette fonction permet de gérer les erreurs dans les formules. Vous pouvez l'utiliser pour afficher un message personnalisé si une recherche de valeur échoue.
Exemple :
=SIERREUR(RECHERCHEV("XYZ-789"; A1:C3; 3; FAUX); "Produit non trouvé")
Cette formule renverra le prix du produit "XYZ-789" si il est trouvé dans la plage A1:C3. Si le produit n'est pas trouvé, elle renverra le message "Produit non trouvé".
Conclusion
La recherche de valeur est une compétence fondamentale pour tout utilisateur d'Excel. En maîtrisant les fonctions RECHERCHEV, RECHERCHEH, INDEX et EQUIV, vous serez en mesure de trouver rapidement et efficacement l'information dont vous avez besoin, même dans les feuilles de calcul les plus complexes. N'hésitez pas à expérimenter avec ces fonctions et à les adapter à vos propres besoins. En pratiquant régulièrement, vous deviendrez un expert de la recherche de valeur dans Excel !