Comment rechercher une valeur dans un tableau Excel efficacement ?

Les bases de la recherche de valeurs dans Excel

Comprendre l'importance de la recherche efficace

Dans le monde de la gestion des données, la capacité à rechercher une valeur dans un tableau Excel rapidement et précisément est cruciale. Une recherche efficace vous permet de :

• Gagner du temps en évitant de parcourir manuellement de vastes feuilles de calcul.
• Réduire le risque d'erreurs humaines lors de l'identification de données.
• Prendre des décisions éclairées basées sur des informations précises.
• Automatiser des tâches répétitives grâce à l'utilisation de formules.

Les fonctions de recherche les plus courantes

Excel offre plusieurs fonctions pour effectuer des recherches, chacune ayant ses propres avantages et inconvénients. Les plus utilisées sont :

• RECHERCHEV (VLOOKUP) : Recherche une valeur dans la première colonne d'un tableau et renvoie une valeur sur la même ligne, dans une colonne spécifiée.
• RECHERCHEH (HLOOKUP) : Similaire à RECHERCHEV, mais recherche une valeur dans la première ligne d'un tableau et renvoie une valeur dans une colonne spécifiée.
• INDEX et EQUIV (INDEX & MATCH) : Une combinaison puissante qui permet une recherche plus flexible et précise que RECHERCHEV.
• RECHERCHE (LOOKUP) : Une fonction plus ancienne, moins flexible que RECHERCHEV et INDEX+EQUIV, mais qui peut être utile dans certains cas simples.
• XRECHERCHE (XLOOKUP) : La fonction la plus récente et la plus polyvalente, disponible dans les versions récentes d'Excel. Elle remplace avantageusement RECHERCHEV et RECHERCHEH.

Utiliser la fonction RECHERCHEV (VLOOKUP)

Syntaxe de RECHERCHEV

La fonction RECHERCHEV (VLOOKUP en anglais) est l'une des plus utilisées pour rechercher une valeur dans un tableau Excel. Sa syntaxe est la suivante :

=RECHERCHEV(valeur_recherchée; table_matrice; no_index_col; [valeur_proche])

• valeur_recherchée : La valeur que vous souhaitez rechercher.
• table_matrice : La plage de cellules dans laquelle effectuer la recherche. La première colonne de cette plage doit contenir les valeurs dans lesquelles vous effectuez la recherche.
• no_index_col : Le numéro de la colonne dans table_matrice qui contient la valeur à renvoyer.
• [valeur_proche] : Un argument facultatif. VRAI (ou omis) pour une correspondance approximative (la première colonne de table_matrice doit être triée par ordre croissant). FAUX pour une correspondance exacte.

Exemple pratique avec RECHERCHEV

Imaginez un tableau contenant une liste de produits avec leurs prix. Vous souhaitez retrouver le prix d'un produit spécifique.

Pour retrouver le prix du produit B, vous utiliserez la formule suivante :

=RECHERCHEV("B";A1:B3;2;FAUX)

• "B" est la valeur recherchée.
• A1:B3 est la table de recherche.
• 2 indique que la valeur à renvoyer se trouve dans la deuxième colonne du tableau.
• FAUX indique que vous souhaitez une correspondance exacte.

Cette formule renverra 20 €.

Erreurs courantes avec RECHERCHEV et comment les éviter

• #N/A : Cette erreur apparaît lorsque la valeur recherchée n'est pas trouvée dans la première colonne de la table. Assurez-vous que la valeur recherchée existe et qu'il n'y a pas de fautes de frappe.
• Mauvais numéro d'index de colonne : Vérifiez que le numéro de la colonne spécifié dans la formule correspond bien à la colonne contenant la valeur à renvoyer.
• Correspondance approximative incorrecte : Si vous utilisez VRAI (ou omettez l'argument valeur_proche), assurez-vous que la première colonne de la table est triée par ordre croissant. Sinon, vous obtiendrez des résultats incorrects.
• Espaces intempestifs : Les espaces avant ou après la valeur recherchée peuvent empêcher la fonction de trouver une correspondance. Utilisez la fonction SUPPRESPACE pour supprimer les espaces inutiles.

Utiliser la fonction RECHERCHEH (HLOOKUP)

Syntaxe de RECHERCHEH

RECHERCHEH est l'équivalent horizontal de RECHERCHEV. Elle recherche une valeur dans la première ligne d'un tableau. Sa syntaxe est :

=RECHERCHEH(valeur_recherchée; table_matrice; no_index_ligne; [valeur_proche])

• valeur_recherchée : La valeur à rechercher.
• table_matrice : La plage de cellules contenant le tableau.
• no_index_ligne : Le numéro de la ligne contenant la valeur à renvoyer.
• [valeur_proche] : VRAI (ou omis) pour une correspondance approximative. FAUX pour une correspondance exacte.

Quand utiliser RECHERCHEH plutôt que RECHERCHEV

Utilisez RECHERCHEH lorsque vos données sont organisées horizontalement, c'est-à-dire que les valeurs de recherche se trouvent dans la première ligne du tableau, et les données associées dans les lignes suivantes.

Exemple pratique avec RECHERCHEH

Considérons le tableau suivant :

Pour retrouver le prix du produit B, vous utiliserez la formule :

=RECHERCHEH("B";A1:D2;2;FAUX)

Cette formule renverra 20 €.

La combinaison INDEX et EQUIV : une alternative plus flexible

Comprendre les fonctions INDEX et EQUIV

La combinaison des fonctions INDEX et EQUIV offre une alternative plus puissante et flexible à RECHERCHEV et RECHERCHEH.

• INDEX : Renvoie la valeur d'une cellule à une position donnée dans une plage ou un tableau.
• EQUIV : Renvoie la position relative d'une valeur dans une plage.

Syntaxe de INDEX et EQUIV

• INDEX : =INDEX(matrice; no_ligne; [no_colonne])
• EQUIV : =EQUIV(valeur_recherchée; plage_recherche; [type_correspondance])

Avantages de INDEX et EQUIV par rapport à RECHERCHEV

• Flexibilité : INDEX et EQUIV ne nécessitent pas que la colonne de recherche soit la première colonne du tableau.
• Moins d'erreurs : Moins sensible aux insertions ou suppressions de colonnes.
• Plus de puissance : Permet des recherches plus complexes.

Exemple pratique avec INDEX et EQUIV

Reprenons le tableau précédent :

Pour retrouver le prix du produit B, vous utiliserez la formule suivante :

=INDEX(B1:B3;EQUIV("B";A1:A3;0))

• EQUIV("B";A1:A3;0) renvoie la position de "B" dans la plage A1:A3 (soit 2).
• INDEX(B1:B3;2) renvoie la valeur de la 2ème cellule dans la plage B1:B3 (soit 20 €).

Cas d'utilisation avancés de INDEX et EQUIV

INDEX et EQUIV peuvent être utilisés pour des recherches plus complexes, comme :

• Rechercher une valeur dans un tableau à deux dimensions.
• Effectuer une recherche basée sur plusieurs critères.

La fonction XRECHERCHE (XLOOKUP) : la nouvelle référence

Présentation de XRECHERCHE

XRECHERCHE est une fonction relativement nouvelle dans Excel (disponible à partir d'Excel 365) qui combine les fonctionnalités de RECHERCHEV, RECHERCHEH et INDEX+EQUIV. Elle est plus simple à utiliser et plus puissante que ses prédécesseurs.

Syntaxe de XRECHERCHE

=XRECHERCHE(valeur_recherchée; plage_recherche; plage_renvoi; [si_non_trouvé]; [mode_correspondance]; [mode_recherche])

• valeur_recherchée : La valeur à rechercher.
• plage_recherche : La plage où rechercher la valeur.
• plage_renvoi : La plage contenant la valeur à renvoyer.
• [si_non_trouvé] : (Facultatif) La valeur à renvoyer si la valeur recherchée n'est pas trouvée.
• [mode_correspondance] : (Facultatif) Le type de correspondance (0 pour une correspondance exacte, -1 pour la plus petite valeur inférieure ou égale à la valeur recherchée, 1 pour la plus grande valeur supérieure ou égale à la valeur recherchée, 2 pour une correspondance avec des caractères génériques).
• [mode_recherche] : (Facultatif) Le mode de recherche (1 pour une recherche de la première à la dernière valeur, -1 pour une recherche de la dernière à la première valeur, 2 pour une recherche binaire en ordre croissant, -2 pour une recherche binaire en ordre décroissant).

Avantages de XRECHERCHE par rapport à RECHERCHEV et INDEX+EQUIV

• Plus simple à utiliser : La syntaxe est plus intuitive et moins sujette aux erreurs.
• Plus flexible : Ne nécessite pas que la colonne de recherche soit la première colonne du tableau.
• Gère les erreurs nativement : L'argument si_non_trouvé permet de spécifier une valeur à renvoyer si la valeur recherchée n'est pas trouvée, évitant ainsi l'erreur #N/A.
• Recherche bidirectionnelle : Peut rechercher de la première à la dernière valeur ou de la dernière à la première.

Exemple pratique avec XRECHERCHE

Reprenons le tableau :

Pour retrouver le prix du produit B, vous utiliserez la formule suivante :

=XRECHERCHE("B";A1:A3;B1:B3)

Cette formule renverra 20 €.

Conseils et astuces pour optimiser vos recherches dans Excel

Utiliser les noms de plages

Au lieu d'utiliser des références de cellules (par exemple, A1:B10), vous pouvez définir des noms de plages. Cela rend vos formules plus lisibles et plus faciles à maintenir. Pour définir un nom de plage, sélectionnez la plage de cellules, puis tapez le nom souhaité dans la zone de nom (à gauche de la barre de formule).

Verrouiller les références de cellules

Lorsque vous copiez une formule, les références de cellules sont automatiquement ajustées. Pour empêcher cela, utilisez le symbole $ pour verrouiller les références (par exemple, $A$1).

Utiliser la validation des données

La validation des données permet de limiter les valeurs qui peuvent être entrées dans une cellule. Cela peut être utile pour éviter les erreurs de saisie et garantir la cohérence des données.

Exploiter les tableaux structurés

Les tableaux structurés (créés via l'onglet Insertion > Tableau) offrent de nombreux avantages, notamment :

• Des références structurées (par exemple, Tableau1[Produit]) qui rendent les formules plus lisibles.
• L'extension automatique des formules lorsque vous ajoutez des lignes ou des colonnes au tableau.
• Des filtres et des tris intégrés.

Combiner les fonctions

N'hésitez pas à combiner les fonctions Excel pour effectuer des recherches plus complexes. Par exemple, vous pouvez utiliser la fonction SI pour gérer les cas où la valeur recherchée n'est pas trouvée.

Conclusion

Rechercher une valeur dans un tableau Excel est une compétence essentielle pour tout utilisateur d'Excel. En maîtrisant les fonctions RECHERCHEV, RECHERCHEH, INDEX+EQUIV et XRECHERCHE, ainsi qu'en appliquant les conseils et astuces présentés dans cet article, vous serez en mesure d'optimiser vos recherches et de gagner un temps précieux dans votre travail quotidien.