Cliquer ici pour télécharger le fichier
Principe : La formule RECHERCHEX combine les formules RECHERCHEV et RECHERCHEH et comble leurs faiblesses.
= RECHERCHEX(valeur_cherchée ; tableau_recherche ; tableau_renvoyé ; [si_non_trouvé] ; [mode_correspondance] ; [mode_recherche])
- La valeur que vous souhaitez retrouver
- La table (colonne ou ligne) dans laquelle on souhaite retrouver la valeur cherchée
- La table (colonne(s) ou ligne(s)) pour laquelle on souhaite extraire le résultat
- [Facultatif] : Le résultat à afficher en cas d’erreur (si la valeur cherchée n’est pas retrouvée)
- [Facultatif] : Le type de correspondance souhaité avec la valeur cherchée
- [Facultatif] : L’ordre dans lequel effectuer la recherche
Caractéristiques de la formule RECHERCHEX :
- Le tableau_recherche et le tableau_renvoyé doivent obligatoirement avoir la même taille.
- Si aucun des arguments facultatifs ne sont renseignés alors par défaut la RECHERCHEX effectue une recherche exacte : mode_correspondance = 0
1) Utilisation de la formule RECHERCHEX comme RECHERCHEV
On a un tableau avec des numéros de patient et on souhaite retrouver la taille en fonction du patient :
= RECHERCHEX (B14 ; $B$3:$B$11 ; $C$3:$C$11)
- C’est la cellule qui contient le numéro de patient pour lequel on souhaite retrouver la taille
- C’est la colonne dans laquelle on souhaite retrouver le numéro de patient
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir la taille du patient
Résultat : La formule RECHERCHEX retrouve le numéro de patient 4128v dans le tableau_recherche et renvoie alors la taille situé sur la même ligne que dans le tableau_renvoyé.
2) Formule RECHERCHEX si non trouvé
En cas d’erreur de la RECHERCHEX (si la valeur cherchée n’est pas retrouvée dans la table de recherche) il suffit de renseigner le paramètre [si_non_trouvé] pour indiquer la valeur à faire apparaître. Pour obtenir le même résultat avec la formule RECHERCHEV il faut la combiner avec la formule SIERREUR.
= Dans l’exemple le numéro de patient 4129v n’est pas retrouvé dans la liste des patients, la formule RECHERCHEX renvoie alors le message d’erreur #N/A.
= RECHERCHEX (B14 ; $B$3:$B$11 ; $C$3:$C$11 ; « N° de patient introuvable »)
- C’est la cellule qui contient le numéro de patient pour lequel on souhaite retrouver la taille
- C’est la colonne dans laquelle on souhaite retrouver le numéro de patient
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir la taille du patient
- Le texte qu’on veut faire apparaître lorsque la valeur cherchée (ici le n° de patient) n’est pas retrouvé : Texte à mettre entre guillemets « »
Résultat : Le numéro de patient 4129v n’est pas retrouvé dans la liste des patients, la formule renvoie alors le texte « N° de patient introuvable ».
3) Modification de la structure de tableau de la valeur cherchée
Si une nouvelle colonne est insérée dans le tableau de recherche cela ne modifie pas le résultat obtenu par la formule RECHERCHEX qui s’adapte. Par contre, les formules RECHERCHEV et RECHERCHEH qui fonctionnent en indiquant un numéro d’index (de colonne pour la formule RECHERCHEV et de ligne pour la formule RECHERCHEH) ne supportent donc pas les insertions et suppressions de colonnes.
Résultat : En cas d’insertion ou de suppression de colonne(s) ou ligne(s) dans le tableau de recherche les formules RECHERCHEV et RECHERCHEH ne renvoie plus le bon résultat (du au décalage de colonne ou de ligne) ; Par contre la formule RECHERCHEX renvoie toujours le bon résultat puisque le tableau_recherche et le tableau_renvoyé s’adaptent à la modification.
4) RECHERCHEX sur la gauche
La formule RECHERCHEV regarde uniquement sur la droite : Elle fonctionne uniquement si la colonne de la valeur cherchée est située à gauche de la table de recherche (du moins à gauche par rapport à la colonne de la valeur à renvoyer en résultat). Si la valeur cherchée est située à droite par rapport à la colonne de la valeur résultat alors la RECHERCHEV ne fonctionne pas.
Pour combler ce problème il fallait utiliser les formules INDEX et EQUIV. Désormais avec la RECHERCHEX il est possible d’effectuer une recherche dans les deux sens.
Résultat : Pour la formule RECHERCHEX le tableau_renvoyé est sélectionné directement dans le tableau, (il n’y a pas de numéro d’index de colonne ou de ligne) il est donc possible de faire une recherche avec le tableau_recherche à droite et le tableau_renvoyé à gauche.
5) Formule propagée
Pour faire apparaître des résultats issus de plusieurs colonnes successives situés sur la même ligne :
- Avec la formule RECHERCHEV il faut reproduire la formule pour chaque nouvelle colonne résultat en modifiant à chaque fois le numéro d’index colonne.
- Avec la formule RECHERCHEX il suffit de sélectionner l’ensemble des colonnes souhaitées comme résultat pour le tableau_renvoyé => Tous les résultats situés dans des colonnes différentes sur la même ligne de la valeur cherchée apparaissent alors sur les différentes colonnes résultats et ce en une seule formule.
= RECHERCHEX (B14 ; $B$3:$B$11 ; $C$3:$E$11)
- C’est la cellule qui contient le numéro de patient pour lequel on souhaite retrouver la taille
- C’est la colonne dans laquelle on souhaite retrouver le numéro de patient
- Ce sont les colonnes dans lesquelles se trouvent les résultats à afficher, à savoir la taille, le poids et l’âge du patient
Résultat : La formule se propage automatiquement sur la droite en fonction du nombre de colonnes sélectionnées dans le paramètre tableau_renvoyé : 3 colonnes dans l’exemple pour retrouver la taille, le poids et l’âge du patient saisi.
6) RECHERCHEX avec un barème
Le paramètre VRAI de la formule RECHERCHEV permet de faire une recherche de la valeur inférieure la plus proche de la valeur cherchée : Les valeurs de la table de recherche doivent donc être rangées par ordre croissant. Par contre il est impossible de faire une RECHERCHEV de la valeur supérieure la plus proche.
La RECHERCHEX quant à elle permet de faire ces 2 types de recherche avec le paramètre [mode_correspondance] :
- -1 = Correspondance exacte ou élément inférieur suivant (pour les barèmes croissants)
- 1 = Correspondance exacte ou élément supérieur suivant (pour les barèmes décroissants)
Exemple 1 : Barème croissant
= RECHERCHEX (C10 ; $B$4:$B$7 ; $C$4:$E$7 ; ; -1)
- C’est la cellule qui contient le total de ventes réalisées pour lequel on souhaite retrouver le taux de remise correspondant
- C’est la colonne dans laquelle on souhaite retrouver le numéro de montant des ventes
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir le taux de remise
- ; ; => On ne renseigne rien pour l’argument facultatif [si_non_trouvé]
- C’est le mode de correspondance choisi : -1 car on effectue une recherche dans un barème croissant
Résultat : La formule affiche le taux de remise applicable en fonction du montant des ventes réalisées : On obtient le même résultat en utilisant le paramètre VRAI de la formule RECHERCHEV.
Exemple 2 : Barème décroissant
= RECHERCHEX (C10 ; $B$4:$B$7 ; $C$4:$E$7 ; ; -1)
- C’est la cellule qui contient le taux de service pour lequel on souhaite retrouver le montant de prime correspondant
- C’est la colonne dans laquelle on souhaite retrouver le taux de service
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir le montant de la prime
- ; ; => On ne renseigne rien pour l’argument facultatif [si_non_trouvé]
- C’est le mode de correspondance choisi : 1 car on effectue une recherche dans un barème décroissant
Résultat : Contrairement à la formule RECHERCHEV, la formule RECHERCHEX permet aussi d’effectuer une recherche dans un barème décroissant (avec des valeurs rangées par ordre décroissant dans la table de recherche).
7) RECHERCHEX avec des caractères inconnus
Comme pour la formule RECHERCHEV il est possible d’effectuer une recherche sans avoir la connaissance exacte de la valeur cherchée ; Pour cela il faut utiliser des caractères génériques (= caractères spéciaux utilisés pour remplacer des caractères inconnus) pour la valeur cherchée.
[mode_correspondance] : 2 = Correspondance de caractère générique
Dans l’exemple on recherche le poids du patient dont le numéro termine par « 28v ».
= RECHERCHEX (« ???28v » ; $B$4:$B$7 ; $C$4:$E$7 ; ; 2)
- C’est le numéro de patient recherché, on sait qu’il contient 6 caractères et qu’il termine par « 28v » = On remplace donc les 3 premiers caractères par « ? »
- C’est la colonne dans laquelle on souhaite retrouver le numéro de patient
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir le poids du patient
- ; ; => On ne renseigne rien pour l’argument facultatif [si_non_trouvé]
- C’est le mode de correspondance choisi : 2 car on effectue une recherche avec des caractères génériques pour la valeur cherchée
Résultat : La formule retrouve bien le poids du patient dont le numéro termine par « 28v ».
8) Choix du mode de recherche
La formule RECHERCHEV et la formule RECHERCHEH permettent de retrouver uniquement le valeur cherchée respectivement à partir de la première ligne et de la première colonne ; Mais elles ne permettent pas de retourner la dernière occurrence d’une série de valeurs.
Par contre la RECHERCHEX permet de choisir l’ordre dans lequel effectuer la recherche dans une série de valeurs grâce au paramètre [mode_recherche] :
- 1 = Rechercher du premier au dernier (paramètre par défaut si non renseigné)
- -1 = Rechercher du dernier au premier : Pour commencer par la fin
Exemple 1 : Recherche du premier au dernier
= RECHERCHEX (C14 ; $B$4:$B$11 ; $C$4:$C$11 ; ; -1 ; 1)
- C’est la cellule qui contient le taux de service pour lequel on souhaite retrouver le montant de prime correspondant
- C’est la colonne dans laquelle on souhaite retrouver le taux de service
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir le montant de la prime
- ; ; => On ne renseigne rien pour l’argument facultatif [si_non_trouvé]
- C’est le mode de correspondance choisi : -1 car on effectue une recherche dans un barème croissant
- C’est le mode de recherche : 1 car on effectue la recherche dans la premier barème du tableau
Résultat : La recherche s’effectue bien dans le premier barème.
Exemple 2 : Recherche du dernier au premier
= RECHERCHEX (C14 ; $B$4:$B$11 ; $C$4:$C$11 ; ; -1 ; 1)
- C’est la cellule qui contient le taux de service pour lequel on souhaite retrouver le montant de prime correspondant
- C’est la colonne dans laquelle on souhaite retrouver le taux de service
- C’est la colonne dans laquelle se trouve le résultat à afficher, à savoir le montant de la prime
- ; ; => On ne renseigne rien pour l’argument facultatif [si_non_trouvé]
- C’est le mode de correspondance choisi : -1 car on effectue une recherche dans un barème croissant
- C’est le mode de recherche : -1 car on effectue la recherche dans le dernier barème du tableau
Résultat : La recherche s’effectue bien dans le dernier barème.
9) Utilisation de la formule RECHERCHEX comme RECHERCHEH
Même principe que pour la RECHERCHEV, la formule RECHERCHEX permet de remplacer la RECHERCHEH en comblant ses lacunes :
- Contrairement à la RECHERCHEH, la formule RECHERCHEX supporte la suppression et l’insertion de lignes dans le tableau de recherche.
- La ligne de la valeur cherchée peut être située n’importe où sur le tableau de recherche alors qu’avec la RECHERCHEH la ligne de la valeur cherchée doit obligatoirement être située au-dessus.
= RECHERCHEX (C7 ; $C$2:$K$2 ; $C$4:$K$4)
- C’est la cellule qui contient le numéro de patient pour lequel on souhaite retrouver la taille
- C’est la ligne dans laquelle on souhaite retrouver le numéro de patient
- C’est la ligne dans laquelle se trouve le résultat à afficher, à savoir la taille du patient
=> Voir aussi comment faire une RECHERCHEV avec plusieurs critères
