La nouvelle fonction XLOOKUP est déployée sur Office 365 à partir de novembre 2019. Joe McDaid de l'équipe Excel a conçu XLOOKUP pour unifier les personnes qui utilisent RECHERCHEV et celles qui utilisent INDEX / MATCH. Cette section présente les 12 avantages de XLOOKUP:
- La correspondance exacte est la valeur par défaut.
- Le troisième argument basé sur des entiers de RECHERCHEV est désormais une référence appropriée.
- IFNA est intégré pour gérer les valeurs manquantes.
- XLOOKUP n'a aucun problème à aller vers la gauche.
- Trouvez la correspondance suivante plus petite ou suivante plus grande sans trier le tableau.
- XLOOKUP peut faire HLOOKUP.
- Trouvez la dernière correspondance en effectuant une recherche par le bas.
- Les caractères génériques sont "désactivés" par défaut, mais vous pouvez les réactiver.
- Renvoyez tous les 12 mois dans une seule formule.
- Peut renvoyer une référence de cellule si XLOOKUP est à côté d'un deux-points tel que XLOOKUP (); XLOOKUP ()
- Peut faire une correspondance bidirectionnelle comme INDEX (, MATCH, MATCH) peut le faire.
- Peut résumer toutes les recherches dans une seule formule comme le ferait LOOKUP.
Voici la syntaxe: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).
XLOOKUP Avantage 1: correspondance exacte par défaut
99% de mes formules RECHERCHEV se terminent par, FALSE ou 0 pour indiquer une correspondance exacte. Si vous utilisez toujours la version de correspondance exacte de VLOOKUP, vous pouvez commencer à laisser le match_mode hors de votre fonction XLOOKUP.
Dans la figure suivante, vous recherchez W25-6 à partir de la cellule A4. Vous voulez rechercher cet élément dans L8: L35. Lorsqu'il est trouvé, vous voulez le prix correspondant de la colonne N. Il n'est pas nécessaire de spécifier False comme match_mode car XLOOKUP par défaut correspond à une correspondance exacte.
XLOOKUP Avantage 2: le Results_Array est une référence au lieu d'un entier
Pensez à la formule VLOOKUP que vous utiliseriez avant XLOOKUP. Le troisième argument aurait été un 3 pour indiquer que vous vouliez retourner la 3e colonne. Il y avait toujours un risque qu'un collègue ignorant insère (ou supprime) une colonne dans votre tableau. Avec une colonne supplémentaire dans le tableau, la RECHERCHEV qui avait renvoyé un prix commencerait à renvoyer une description. Comme XLOOKUP pointait vers une référence de cellule, la formule se réécrit pour continuer à pointer vers le prix qui se trouve maintenant dans la colonne O.
XLOOKUP Benefit 3: IFNA est intégré en tant qu'argument facultatif
L'erreur redoutée # N / A est renvoyée lorsque votre valeur de recherche est introuvable dans la table. Dans le passé, pour remplacer # N / A par autre chose, vous deviez utiliser IFERROR ou IFNA enroulé autour de VLOOKUP.
Grâce à une suggestion de Rico sur ma chaîne YouTube, l'équipe Excel a incorporé un quatrième argument optionnel pour if_not_found. Si vous souhaitez remplacer ces erreurs # N / A par zéro, ajoutez simplement 0 comme quatrième argument. Ou, vous pouvez utiliser du texte, tel que "Valeur non trouvée".
XLOOKUP Benefit 4: Aucun problème en regardant à gauche du champ clé
RECHERCHEV ne peut pas regarder à gauche du champ clé sans recourir à RECHERCHEV (A4, CHOISIR ((1,2), G7: G34, F7: F34), 2, Faux). Avec XLOOKUP, il n'y a aucun problème à avoir le Results_array à gauche de Lookup_array.
XLOOKUP Benefit 5: correspondance suivante plus petite ou suivante plus grande sans tri
RECHERCHEV avait une option pour rechercher la correspondance exacte ou simplement une valeur plus petite. Vous pouvez soit laisser le quatrième argument en dehors de VLOOKUP, soit changer le False en True. Pour que cela fonctionne, la table de recherche devait être triée par ordre croissant.
Mais VLOOKUP n'avait pas la possibilité de renvoyer la correspondance exacte ou l'élément suivant plus grand. Pour cela, vous avez dû passer à l'utilisation de MATCH avec un -1 comme match_mode et vous devez faire attention à ce que la table de recherche soit triée par ordre décroissant.
Le cinquième argument optionnel de XLOOKUP match_mode peut rechercher uniquement la correspondance exacte, égale ou juste plus petite, égale ou juste supérieure. Notez que les valeurs dans XLOOKUP ont plus de sens que dans MATCH:
- -1 trouve la valeur égale ou juste inférieure
- 0 trouver une correspondance exacte
- 1 trouve la valeur égale ou juste supérieure.
Mais le plus étonnant: la table de recherche n'a pas besoin d'être triée et tout match_mode fonctionnera.
Ci-dessous, un match_mode de -1 trouve l'élément le plus petit suivant.
Ici, un match_mode de 1, trouve quel véhicule est nécessaire en fonction du nombre de personnes dans le groupe. Notez que la table de recherche n'est pas triée par passagers et que le nom du véhicule se trouve à gauche de la clé.
Le tableau dit:
- Le bus peut accueillir 64 personnes
- La voiture peut contenir 4 personnes
- La moto peut contenir 1 personne
- Tour Van peut accueillir 12 personnes
- Van pour 6 personnes.
En prime, les données sont triées par véhicule (dans l'ancienne solution, en utilisant MATCH, le tableau devrait être trié en ordre décroissant par capacité. Aussi: le véhicule est à gauche de Capacité.
XLOOKUP Benefit 6: Sideways XLOOKUP remplace HLOOKUP
Lookup_array et results_array peuvent être horizontaux avec XLOOKUP, ce qui simplifie le remplacement de HLOOKUP.
XLOOKUP Benefit 7: Recherche par le bas pour le dernier match
J'ai une vieille vidéo sur YouTube qui répond à une question d'une ferme équestre britannique. Ils avaient une flotte de véhicules. Chaque fois qu'un véhicule est entré pour le carburant ou le service, ils ont enregistré le véhicule, la date et le kilométrage dans une feuille de calcul. Ils voulaient trouver le dernier kilométrage connu pour chaque véhicule. Alors que l'ère Excel-2017 MAXIFS pourrait résoudre ce problème aujourd'hui, la solution il y a de nombreuses années était une formule mystérieuse utilisant LOOKUP et impliquait une division par zéro.
Aujourd'hui, le sixième argument facultatif de XLOOKUP vous permet de spécifier que la recherche doit commencer à partir du bas de l'ensemble de données.
Remarque
Bien que ce soit une grande amélioration, cela ne vous permet de trouver que le premier ou le dernier match. Certaines personnes espéraient que cela vous permettrait de trouver la deuxième ou la troisième correspondance, mais ce n'est pas l'intention de l'argument search_mode.
Mise en garde
La figure ci-dessus montre qu'il existe des modes de recherche utilisant l'ancienne recherche binaire. Joe McDaid déconseille de les utiliser. Premièrement, l'algorithme de recherche amélioré de 2018 est suffisamment rapide pour qu'il n'y ait aucun avantage de vitesse significatif. Deuxièmement, vous courez le risque qu'un collègue désemparé trie la table de recherche et introduit de mauvaises réponses.
XLOOKUP Benefit 8: les caractères génériques sont "désactivés" par défaut
La plupart des gens ne savaient pas que RECHERCHEV traite l'astérisque, le point d'interrogation et le tilde comme des caractères génériques comme décrit dans «# 51 Utiliser un caractère générique dans RECHERCHEV» à la page 143. Avec XLOOKUP, les caractères génériques sont désactivés par défaut. Si vous voulez que XLOOKUP traite ces caractères comme un caractère générique, utilisez 2 comme Match_Mode.
XLOOKUP Benefit 9: Retournez les 12 mois en une seule formule!
C'est vraiment un avantage des tableaux dynamiques, mais c'est ma raison préférée d'aimer XLOOKUP. Lorsque vous devez renvoyer les 12 mois dans une recherche, une seule formule entrée dans B6 avec un return_array rectangulaire renverra plusieurs résultats. Ces résultats se répandront dans les cellules adjacentes.
Dans la figure ci-dessous, une seule formule entrée dans B7 renvoie les 12 réponses indiquées dans B7: M7.
XLOOKUP Benefit 10: Peut renvoyer une référence de cellule si elle est adjacente à Colon
Celui-ci est complexe mais beau. Dans le passé, sept fonctions passaient du renvoi d'une valeur de cellule au renvoi d'une référence de cellule si la fonction touchait un deux-points. Pour un exemple, consultez Utiliser A2: INDEX () comme OFFSET non volatile. XLOOKUP est la fonction huit pour offrir ce comportement, joignant CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET et SWITCH.
Considérez la figure suivante. Quelqu'un choisit Cherry en E4 et Fig en E5. Vous voulez une formule qui résume tout de B6 à B9.
Dans la figure ci-dessus, vous pouvez voir qu'une XLOOKUP de E4 renverra le 15 de la cellule B6. Un XLOOKUP de E5 renverra le 30 de B9. Cependant, si vous prenez les deux fonctions XLOOKUP des cellules D9 et D10 et les mettez ensemble avec un deux-points entre les deux, le comportement de XLOOKUP change. Au lieu de renvoyer 15, le premier XLOOKUP renvoie l'adresse de cellule B6!
Pour le prouver, j'ai sélectionné D7 et utilisé Formulas, Evaluate Formula. Après avoir appuyé deux fois sur Evaluer, la prochaine partie à calculer est XLOOKUP ("Cherry", A4: A29, B4: B29), comme illustré ici.
Appuyez à nouveau sur Evaluer et étonnamment, la formule XLOOKUP renvoie 6 $ B $ au lieu des 15 stockés dans B6. Cela se produit car il y a un deux-points immédiatement après cette formule XLOOKUP.
Appuyez encore deux fois sur Évaluer et la formule provisoire sera = SOMME (B6: B9).
C'est un comportement étonnant que la plupart des gens ne connaissent pas. Excel MVP Charles Williams me dit qu'il peut être déclenché avec l'un de ces trois opérateurs à côté de XLOOKUP:
- Côlon
- Espace (opérateur d'intersection)
- Virgule (opérateur de l'Union)
XLOOKUP Benefit 11: Match bidirectionnel comme INDEX (, MATCH, MATCH)
Pour tous mes amis RECHERCHEV, les personnes INDEX / MATCH attendaient de voir si XLOOKUP pouvait gérer une correspondance bidirectionnelle. La bonne nouvelle: il peut le faire. La mauvaise nouvelle: la méthodologie est un peu différente de celle attendue par les fans d'INDEX / MATCH. C'est peut-être un peu au-dessus de leurs têtes. Mais je suis sûr qu'ils peuvent arriver à cette méthode.
Pour une correspondance bidirectionnelle, vous voulez trouver quelle ligne contient le numéro de compte A621 indiqué dans J3. Donc, XLOOKUP commence assez facilement: = XLOOKUP (J3, A5: A15. Mais vous devez ensuite fournir un results_array. Vous pouvez utiliser la même astuce que dans XLOOKUP Benefit 9: Return All 12 Month in a Single Formula ci-dessus, mais utilisez-le pour renvoyer un vecteur vertical. Un XLOOKUP interne recherche le mois J4 dans les en-têtes de mois dans B4: G4. Le return_array est spécifié comme B5: G15. Le résultat est que le XLOOKUP interne renvoie un tableau comme celui indiqué dans I10 : I20 ci-dessous. Puisque A621 se trouve dans la cinquième cellule de lookup_array et 104 se trouve dans la cinquième cellule du results_array, vous obtenez la bonne réponse à partir de la formule. Ci-dessous, J6 montre l'ancienne méthode. J7 renvoie la nouvelle méthode.
XLOOKUP Benefit 12: Additionnez toutes les valeurs de recherche dans une seule formule
L'ancienne fonction LOOKUP offrait deux astuces étranges. Premièrement, si vous essayez de déterminer le montant total des frais de bonus à accumuler, vous pouvez demander à LOOKUP de rechercher toutes les valeurs dans une seule formule. Dans l'image ci-dessous, LOOKUP (C4: C14 effectue 11 recherches. Mais la fonction LOOKUP n'offrait pas de correspondance exacte et nécessitait le tri de la table de recherche.
Avec XLOOKUP, vous pouvez spécifier une plage comme lookup_value et XLOOKUP renverra toutes les réponses. L'avantage est que XLOOKUP peut faire des correspondances exactes.
Astuce bonus: qu'en est-il d'un LOOKUP Twisted?
Excel MVP Mike Girvin montre souvent une astuce de la fonction LOOKUP où le Lookup_Vector est vertical et le Result_Vector est horizontal. XLOOKUP ne supportera pas nativement cette astuce. Mais, si vous trichez un peu et enveloppez le results_array dans la fonction TRANSPOSE, vous pouvez gérer une recherche tordue.
