Cliquer ici pour télécharger le fichier
Principe : La formule Excel RECHERCHEV permet de rechercher une valeur dans la première colonne d’une table de données puis de renvoyer la valeur d’une cellule qui se situe sur la même ligne que la valeur recherchée.
La formule RECHERCHEV est parfaite quand il n’y a qu’une seule correspondance avec la valeur cherchée, par contre, dès lors qu’il y a plusieurs résultats de la valeur cherchée la formule RECHERCHEV ne renvoie qu’un seul résultat parmi toutes les correspondances.
Mais il y a une solution pour retrouver tous les résultats qui correspondent à la valeur cherchée.
Exemple : On a un tableau récapitulatif des kilomètres parcourus par plusieurs véhicules d’une entreprise et on souhaite retrouver tous les trajets effectués par le véhicule sélectionné dans la liste.
1) Test de la formule RECHERCHEV
= RECHERCHEV($G$3 ; $B$3:$D$16 ; 3 ; FAUX)
- $G$3 = C’est la cellule dans laquelle on a crée la liste déroulante pour choisir un véhicule parmi tous les véhicules de l’entreprise.
- $B$3:$D$16 = C’est la table dans laquelle on souhaite retrouver la plaque d’immatriculation du véhicule sélectionné dans la liste.
- 3 = C’est la colonne dans laquelle se trouve le nombre de km parcourus par le véhicule (colonne num 3 en partant de la 1ère colonne du tableau)
- FAUX = car on souhaite une correspondance exacte avec la valeur cherchée.
Résultat : La formule renvoie à chaque fois le même résultat (57 km) puisque c’est la première correspondance avec la valeur cherchée (Immatriculation XP30DM) retrouvée par la formule RECHERCHEV en partant du haut du tableau.
2) RECHERCHEV avec plusieurs résultats
Étape 1 : Il faut attribuer une clé unique pour chaque résultat similaire pour que la formule RECHERCHEV puisse par la suite les rechercher un à un et tous les renvoyer.
=> Pour commencer on utilise la formule NB.SI pour compter le nombre de fois où apparaissent les immatriculations des véhicules dans le tableau ligne après ligne (même principe que pour réaliser un cumul de SOMME).
= NB.SI($C$3:C3 ; C3)
- $C$3:C3 = C’est la plage de cellules dans laquelle on souhaite compter le nombre de fois où on trouve l’immatriculation du véhicule de la ligne : On fige le point de départ de la plage et on laisse en référence relative le point d’arrivé pour compter au fur et à mesure des lignes du tableau.
- C3 = C’est la cellule dans laquelle se trouve l’immatriculation pour compter le nombre de fois où elle apparaît dans la plage de cellule définie.
=> Il faut maintenant combiner ce numéro avec l’immatriculation du véhicule pour créer une clé unique.
= C3 & “_” & NB.SI($C$3:C3 ; C3)
- C3 = C’est la cellule du tableau qui contient la plaque d’immatriculation du véhicule.
- &“_”& = Le tiret bas sert à relier l’immatriculation et le numéro (Ce n’est pas obligatoire d’ajouter un tiret entre les 2 valeurs, le “&” seul suffit pour lier les 2 résultats mais le tiret permet de faciliter la lecture de la nouvelle valeur créée)
- NB.SI($C$3:C3 ; C3) = Permet de compter le nombre d’apparitions progressives de l’immatriculation dans le tableau.
Résultat : On a crée une clé unique pour les véhicules de la liste ; Les immatriculations qui apparaissent plusieurs fois dans le tableau sont désormais différencier en fonction du nombre d’apparition ligne par ligne.
Par exemple l’immatriculation XP30DM apparaît trois fois dans le tableau et on a donc les valeurs suivantes :
- XP30DM_1
- XP30DM_2
- XP30DM_3
Étape 2 : Il ne reste plus qu’à effectuer une RECHERCHEV à partir de cette nouvelle colonne créée.
= RECHERCHEV($H$3 & “_” & G7 ; $B$3:$E$16 ; 4 ; FAUX)
- $H$3 & “_” & G7 = C’est la valeur cherchée : L’immatriculation (en référence absolue) + l’occurrence (en référence relative pour incrémenter le numéro de l’occurrence)
- $B$3:$E$16 = C’est la table dans laquelle on souhaite retrouver la plaque d’immatriculation du véhicule sélectionné dans la liste avec tout à gauche la nouvelle colonne créée avec la valeur composée.
- 4 = C’est la colonne dans laquelle se trouve le nombre de km parcourus par le véhicule (colonne num 4 en partant de la 1ère colonne du tableau)
=> À cette formule on peut rajouter une formule SIERREUR pour les occurrences qui n’existe pas.
= SIERREUR( RECHERCHEV($H$3 & “_” & G7 ; $B$3:$E$16 ; 4 ; FAUX) ; « »)
Dans l’exemple l’immatriculation XP30DM n’est retrouvée que 3 fois et donc pour la 4ème occurrence la formule renvoie une erreur ; Grâce à la formule SIERREUR on remplace le message d’erreur par un vide.
Étape 3 : Il faut compter le nombre d’occurrences pour contrôler les résultats.
= NB.SI( $C$3:$C$16 ; H3)
Résultat : Si on sélectionne un autre véhicule dans la liste on obtient les km correspondant. Cependant dans l’exemple on constate qu’il y a 5 résultats trouvés par la formule NB.SI et seulement 4 occurrences dans le tableau ; Il faut donc dans ce cas rajouter une ligne supplémentaire dans la colonne « Occurrences ».
=> Voir aussi comment faire une RECHERCHEV avec plusieurs critères
