Rechercher vers la gauche et depuis le bas

L’une des principales limites de VLOOKUP était de ne pouvoir renvoyer des valeurs que vers la droite de la colonne de recherche. XLOOKUP n’impose pas cette règle.

Comme vous fournissez séparément le tableau de recherche et le tableau de renvoi, la réponse peut se trouver n’importe où : à gauche, à droite, au-dessus ou en dessous de la colonne recherchée.

Supposons que les ID se trouvent dans la colonne B et les noms dans la colonne A, à gauche. VLOOKUP ne pouvait pas effectuer cette recherche sans réorganiser les colonnes.

Avec XLOOKUP, il vous suffit de rechercher dans la colonne B et de renvoyer la colonne A. La fonction renvoie sans difficulté une valeur située à gauche de la colonne de recherche.

XLOOKUP possède un cinquième argument facultatif, search_mode, qui contrôle la direction de la recherche.

• 1 : recherche du premier au dernier élément (par défaut)
• -1 : recherche du dernier au premier élément
• 2 : recherche binaire dans des données croissantes
• -2 : recherche binaire dans des données décroissantes

Par défaut, XLOOKUP recherche du haut vers le bas et renvoie la première correspondance rencontrée.

Si une valeur apparaît plusieurs fois dans votre liste, ce comportement par défaut vous donne la première occurrence. Cela convient la plupart du temps, mais vous voudrez parfois obtenir l’entrée la plus récente.

Définissez le mode de recherche sur -1 pour effectuer une recherche du bas vers le haut. XLOOKUP renvoie alors la dernière valeur correspondante.

Comme la valeur si introuvable et le mode de correspondance précèdent le mode de recherche, vous devez inclure les arguments précédents comme valeurs d’emplacement, même s’ils sont vides.

Imaginez un journal dans lequel chaque ligne enregistre une mise à jour de l’état d’une commande, la plus ancienne se trouvant en haut.

Une recherche de haut en bas donne le premier état de la commande. Une recherche de bas en haut avec -1 donne son état le plus récent. Les mêmes données peuvent donc produire deux réponses très différentes selon la direction choisie.

Les arguments sont positionnels : vous ne pouvez donc pas passer directement au sixième sans renseigner ceux qui le précèdent.

Dans =XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1), "Not found" correspond à la valeur si introuvable et 0 au mode de correspondance (correspondance exacte). Ces valeurs occupent les emplacements nécessaires pour que -1 soit utilisé comme mode de recherche.

Le voisin du cinquième argument, le mode de correspondance, contrôle la façon dont les correspondances sont effectuées :

• 0 : correspondance exacte (par défaut)
• -1 : correspondance exacte ou valeur immédiatement inférieure
• 1 : correspondance exacte ou valeur immédiatement supérieure
• 2 : correspondance avec caractères génériques utilisant * et ?

Pour les recherches vers la gauche et depuis le bas, vous conservez généralement la valeur 0.

Une feuille de suivi des changements de prix répertorie chaque produit à chaque modification de son prix. Pour obtenir le prix actuel, vous devez prendre la dernière entrée correspondant à ce produit.

Une recherche depuis le bas avec -1 renvoie le prix le plus récent, sans tri ni colonnes auxiliaires supplémentaires.

Les modes de recherche 2 et -2 utilisent une recherche binaire plus rapide, mais ils exigent que le tableau de recherche soit trié : dans l’ordre croissant pour 2 et dans l’ordre décroissant pour -2.

Sur d’immenses listes triées, cette méthode est beaucoup plus rapide. Si les données ne sont pas réellement triées, la recherche binaire peut renvoyer des résultats incorrects ; utilisez-la donc avec prudence.

Vous contrôlez désormais à la fois où et dans quelle direction XLOOKUP effectue ses recherches :

• Renvoyer librement des valeurs situées à gauche
• 1 pour la première correspondance (par défaut)
• -1 pour la dernière correspondance
• 2 ou -2 pour une recherche binaire rapide dans des données triées

N’oubliez pas de remplir les emplacements de la valeur si introuvable et du mode de correspondance avant celui du mode de recherche.

Vérifiez votre compréhension de l’argument de mode de recherche.

Vous avez découvert la puissance directionnelle de XLOOKUP :

• Les tableaux de renvoi peuvent se trouver à gauche du tableau de recherche
• Le mode de recherche (le sixième argument) définit la direction
• -1 trouve la dernière correspondance, ce qui est idéal pour rechercher l’enregistrement le plus récent
• 2 et -2 activent une recherche binaire rapide dans des données triées

Vous allez maintenant renvoyer des lignes ou des colonnes entières avec un seul XLOOKUP.