La formule RECHERCHEX

📩 Télécharger gratuitement le fichier Excel du tutoriel :
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 :

La formule RECHERCHEX

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

La formule RECHERCHEX

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.

La formule RECHERCHEX

= 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.

La formule RECHERCHEX

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 “”

La formule RECHERCHEX

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.

La formule RECHERCHEX

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 dans la mesure où tableau_recherche et tableau_renvoyé sont en référence absolue.


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.

La formule RECHERCHEX

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.


4) 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.

La formule RECHERCHEX

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

La formule RECHERCHEX

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.


5) 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

La formule RECHERCHEX

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

La formule RECHERCHEX

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

La formule RECHERCHEX

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

La formule RECHERCHEX

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).


6) 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”.

La formule RECHERCHEX

RECHERCHEX (“???28v” ; $B$4:$B$7 ; $C$4:$E$7 ; ; -1)

  • 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

La formule RECHERCHEX

Résultat La formule retrouve bien le poids du patient dont le numéro termine par “28v”.


7) 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

La formule RECHERCHEX

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

La formule RECHERCHEX

Résultat La recherche s’effectue bien dans le premier barème.


Exemple 2 : Recherche du dernier au premier

La formule RECHERCHEX

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

La formule RECHERCHEX

Résultat La recherche s’effectue bien dans le dernier barème.


8) 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.

La formule RECHERCHEX

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

La formule RECHERCHEX


=> Voir aussi comment faire une RECHERCHEV avec plusieurs critères