Rechercher la dernière valeur correspondante

La plupart des recherches renvoient la première correspondance trouvée. Mais vous pouvez parfois vouloir la dernière : le prix le plus récent d’un produit, la dernière mise à jour de son état ou la dernière entrée associée à un client.

Lorsqu’une liste s’allonge au fil du temps et qu’une même clé apparaît plusieurs fois, la ligne la plus basse est généralement la plus récente. Une recherche VLOOKUP standard ou un MATCH avec correspondance exacte sélectionnera obstinément la première ligne.

Cette leçon présente plusieurs méthodes fiables pour récupérer la valeur correspondant à la dernière occurrence.

MATCH(value, range, 0) parcourt les lignes de haut en bas et s’arrête à la toute première correspondance exacte. Si « Apple » apparaît aux lignes 2, 5 et 9, MATCH renvoie 2.

C’est parfait lorsque les clés sont uniques, mais les lignes plus récentes sont ignorées. Pour atteindre la dernière occurrence, il faut une technique qui recherche de bas en haut ou qui renvoie la position de la dernière correspondance.

Si vous disposez d’une version récente d’Excel ou de Google Sheets, XLOOKUP simplifie cette opération. Ses cinquième et sixième arguments contrôlent le mode de correspondance et le sens de recherche.

Transmettez -1 comme argument du mode de recherche pour effectuer la recherche de la dernière occurrence vers la première. XLOOKUP renvoie alors la valeur associée à la clé correspondante située le plus bas.

Ici, la fonction recherche le produit indiqué dans G1 parmi A2:A10 et renvoie le prix correspondant de B2:B10, en commençant par le bas.

Dans les feuilles de calcul plus anciennes, une astuce bien connue utilise LOOKUP avec le nombre 2 et une division ingénieuse par une condition.

L’expression 1/(A2:A10=G1) produit 1 pour les lignes correspondantes et une erreur de division pour les autres. LOOKUP recherche 2, une valeur supérieure à toutes les valeurs présentes, ignore les erreurs et atteint le dernier 1 valide, puis renvoie la valeur correspondante de B2:B10.

Examinons 1/(A2:A10=G1) étape par étape :

• Les lignes dont la clé correspond donnent 1/TRUE = 1.
• Les lignes qui ne correspondent pas donnent 1/FALSE = une erreur #DIV/0!.

LOOKUP ignore les erreurs et, lorsqu’elle ne trouve pas sa cible (2), renvoie le résultat aligné sur la dernière entrée sans erreur. Comme toutes les correspondances valent 1, le dernier 1 l’emporte : vous obtenez donc la valeur de la dernière ligne correspondante.

Vous pouvez également rester dans la famille INDEX-MATCH. L’idée consiste à trouver la position de la dernière correspondance, puis à la transmettre à INDEX.

En utilisant la même astuce de division dans MATCH, recherchez 2 dans 1/(A2:A10=G1) afin d’obtenir la position de ligne de la dernière correspondance. Transmettez cette position à INDEX appliqué à la colonne de retour.

Lorsque le troisième argument de MATCH est omis, sa valeur par défaut est 1, ce qui signifie une correspondance approximative sur des données croissantes. MATCH recherche alors la plus grande valeur inférieure ou égale à 2.

Le tableau 1/(A2:A10=G1) contient uniquement des 1 et des erreurs. La plus grande valeur inférieure ou égale à 2 est 1, et MATCH renvoie la position du dernier 1 de ce type. Cette position correspond exactement à la dernière ligne correspondante.

Supposons que A2:A10 répertorie les états de la commande « Order-7 » enregistrés au fil du temps et que B2:B10 contienne le texte de l’état. « Order-7 » apparaît aux lignes 3, 6 et 9.

• Le tableau de correspondance marque les lignes 3, 6 et 9 par un 1, et les autres par des erreurs.
• MATCH(2, ...) renvoie 9 comme position, en comptant depuis le début de la plage : il s’agit de la dernière correspondance.
• INDEX renvoie l’état de cette dernière ligne, c’est-à-dire le plus récent.

Quelle méthode devez-vous utiliser ?

• XLOOKUP avec -1 : la solution la plus claire et la plus lisible si votre application la prend en charge.
• LOOKUP(2, 1/...) : fonctionne presque partout, sans nécessiter de version particulière.
• INDEX-MATCH(2, 1/...) : pratique lorsque vous avez également besoin de la position ou que vous souhaitez renvoyer une valeur depuis une autre colonne.

Les trois méthodes donnent le même résultat ; choisissez en fonction de vos outils et du niveau de lisibilité souhaité pour la formule.

Faites attention aux problèmes suivants :

• Tailles de plages différentes : la plage de conditions et la plage de retour doivent avoir la même hauteur, sinon les lignes sont décalées.
• Doublons masqués : les espaces finaux peuvent faire en sorte que « Apple » soit différent de « Apple » ; nettoyez d’abord le texte avec TRIM.
• Aucune correspondance : l’astuce renvoie une erreur si rien ne correspond. Entourez-la de IFERROR pour prévoir une solution de repli conviviale.

Vous pouvez combiner l’astuce de la dernière correspondance avec deux conditions. Multipliez les tests de conditions à l’intérieur de la division, afin que seules les lignes qui satisfont les deux clés produisent un 1.

Par exemple, recherchez le prix le plus récent pour lequel le produit est égal à G1 et la région est égale à G2. L’astuce LOOKUP(2, ...) atteint toujours la dernière ligne admissible.

C’est pratique pour les journaux horodatés où le même produit apparaît dans plusieurs régions.

Vérifiez votre compréhension des recherches de la dernière correspondance.

Pour renvoyer la valeur correspondant à la dernière occurrence plutôt qu’à la première :

• Utilisez XLOOKUP(..., -1) pour rechercher de bas en haut lorsque cette fonction est disponible.
• Utilisez l’astuce classique LOOKUP(2, 1/(range=key), result) avec n’importe quelle version.
• Utilisez INDEX(result, MATCH(2, 1/(range=key))) lorsque vous avez également besoin de la position.

N’oubliez pas de conserver des plages de même taille, de supprimer les espaces superflus et d’entourer la formule de IFERROR par sécurité.