La fonction XLOOKUP est exclusive au programme d’initiés d’Office 365.

La fonction LOOKUP a de nombreuses fonctionnalités qui surmontent de nombreuses faiblesses des fonctions RECHERCHEV et RECHERCHEH, mais malheureusement, elles ne nous sont pas disponibles pour le moment. Mais ne vous inquiétez pas, nous pouvons créer une fonction XLOOKUP qui fonctionne exactement de la même manière que la prochaine fonction XLOOKUP MS Excel. Nous y ajouterons des fonctionnalités une par une.

Code VBA de la fonction XLOOKUP La fonction de recherche UDF ci-dessous résoudra de nombreux problèmes. Copiez-le ou téléchargez le complément xl ci-dessous le fichier ci-dessous.

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

Explication:

Le code ci-dessus est simplement INDEX-MATCH de base utilisé dans VBA. Cela simplifie beaucoup de choses auxquelles un nouvel utilisateur est confronté. Si résout la complexité de la fonction INDEX-MATCH et n’utilise que trois arguments. Vous pouvez le copier dans votre fichier Excel ou télécharger le fichier .xlam ci-dessous et l’installer en tant que complément Excel. Si vous ne savez pas comment créer et utiliser un complément, `link: / excel-macros-and-vba-add-in-in-vb [cliquez ici, cela vous aidera].

image 48XLOOKUP Add-In]

voyons comment cela fonctionne sur une feuille de calcul Excel.

Syntaxe de XLOOKUP

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value: C’est la valeur que vous souhaitez rechercher dans le

lookup_array.

lookup_array: Il s’agit d’une plage unidimensionnelle dans laquelle vous souhaitez rechercher la valeur de recherche. result_array: C’est aussi une plage unidimensionnelle. Il s’agit de la plage à partir de laquelle vous souhaitez récupérer la valeur.

Voyons cette fonction XLOOKUP en action.

XLOOKUP Exemples:

image

Ici, j’ai une table de données dans Excel. Explorons certaines fonctionnalités en utilisant ce tableau de données.

Fonctionnalité 1.ExactLookup sur les côtés gauche et droit de la valeur de recherche. Comme nous savons que la fonction Excel VLOOKUP ne peut pas récupérer les valeurs à gauche de la valeur de recherche. Pour cela, vous devez utiliser la combinaison complexe INDEX-MATCH. Mais plus maintenant.

image

En supposant que nous devons récupérer toutes les informations disponibles dans le tableau de certains numéros de rôle. Dans ce cas, vous devrez également récupérer la région, qui se trouve à gauche de la colonne du numéro de rouleau.

Écrivez cette formule, I2:

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

Nous obtenons le résultat Nord pour le rouleau numéro 112. Copiez ou faites glisser la formule dans les cellules ci-dessous pour les remplir avec les régions respectives.

Comment ça marche?

Le mécanisme est simple. Cette fonction recherche la valeur lookup_value dans lookup_array et renvoie l’index d’une première correspondance exacte. Utilise ensuite cet index pour récupérer la valeur de result_array. Cette fonction fonctionne parfaitement avec les plages nommées.

De même, utilisez cette formule pour récupérer la valeur de chaque colonne.

image

Fonctionnalité 2. ExactHorizontalLookup au-dessus et au-dessous de la valeur de recherche. XLOOKUP fonctionne également comme une fonction HLOOKUP exacte. La fonction RECHERCHEH a la même limitation que la RECHERCHEV. Il ne peut pas récupérer la valeur au-dessus de la valeur de recherche. Mais XLOOKUP ne fonctionne pas seulement comme HLOOKUP, il surmonte également cette faiblesse. Voyons comment.

Hypothétiquement, si vous souhaitez comparer deux enregistrements. L’enregistrement de recherche que vous avez déjà. L’enregistrement avec lequel vous souhaitez comparer se trouve au-dessus de lookup_range. Utilisez cette formule dans ce cas.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

faites glisser la formule vers le bas et vous obtenez l’enregistrement complet de la ligne de comparaison.

image

Fonctionnalité 3. Pas besoin de numéro de colonne et de correspondance exacte par défaut.

Lorsque vous utilisez la fonction RECHERCHEV, vous devez indiquer le numéro de colonne à partir duquel vous souhaitez récupérer les valeurs. Pour cela, il faut compter les colonnes ou utiliser quelques astuces, prendre l’aide d’autres fonctions. Avec cette UDF XLOOKUP, vous n’avez pas besoin de le faire.

Si vous utilisez RECHERCHEV pour simplement récupérer une valeur dans une colonne ou pour vérifier si une valeur existe dans la colonne, c’est la meilleure solution selon moi.

Fonctionnalité 4. Remplace la fonction INDEX-MATCH, VLOOKUP, HLOOKUP

Pour les tâches simples, notre fonction XLOOKUP remplace les fonctions mentionnées ci-dessus.

Limitations de XLOOKUP:

Quand il s’agit de formules complexes, comme //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP with Dynamic Col Index] `où nous VLOOKUP identifie la colonne de recherche avec des en-têtes, cette XLOOKUP échouera.

Une autre limitation est que si vous devez rechercher plusieurs colonnes ou lignes aléatoires dans la table, cette fonction sera inutile car vous devrez écrire cette formule encore et encore. Ceci peut être surmonté en utilisant link: / excel-range-name-all-about-excel-named-ranges-excel-range-name [named ranges].

Pour l’instant, nous n’avons pas ajouté la fonctionnalité approximative, donc bien sûr, vous ne pouvez pas obtenir la correspondance approximative. Nous ajouterons cela trop tôt.

Si la fonction XLOOKUP ne parvient pas à trouver la valeur de recherche, elle renverra une erreur #VALUE et non # N / A.

Alors oui les gars, voici comment vous utilisez XLOOKUP pour récupérer, rechercher et valider des valeurs dans des tables Excel. Vous pouvez utiliser cette fonction définie par l’utilisateur pour une recherche sans tracas à gauche ou en haut de la valeur de recherche. Si vous avez encore des doutes ou des exigences spécifiques liées à cette fonction ou à la requête liée à EXCEL 2010/2013/2016/2019/365 ou VBA, demandez-le dans la section commentaires ci-dessous. Vous obtiendrez sûrement une réponse.

Articles liés:

lien: / custom-functions-in-vba-create-vba-function-to-return-array [Créer une fonction VBA pour renvoyer un tableau] | Pour renvoyer un tableau à partir d’une fonction définie par l’utilisateur, nous devons le déclarer lorsque nous nommons l’UDF.

link: / excel-array-formulas-arrays-in-excel-formula [Tableaux dans Excel Formul] | Apprenez quels tableaux sont dans Excel.

link: / vba-user-defined-function [Comment créer une fonction définie par l’utilisateur via VBA] | Apprenez à créer des fonctions définies par l’utilisateur dans Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Using a User Defined Function (UDF) from another workbook using VBA dans Microsoft Excel] `| Utilisez la fonction définie par l’utilisateur dans un autre classeur d’Excel `link: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Renvoie les valeurs d’erreur de l’utilisateur fonctions utilisant VBA dans Microsoft Excel] `| Découvrez comment vous pouvez renvoyer des valeurs d’erreur à partir d’une fonction définie par l’utilisateur

Articles populaires:

lien: / general-topics-in-vba-split-excel-sheet-into-multiple-files-based-on-column-using-vba [Diviser la feuille Excel en plusieurs fichiers basés sur la colonne en utilisant VBA] | Cette feuille Excel fractionnée par code VBA est basée sur des valeurs uniques dans une colonne spécifiée.

Téléchargez le fichier de travail.

lien: / general-topics-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Désactiver les messages d’avertissement à l’aide de VBA dans Microsoft Excel 2016] | Pour désactiver les messages d’avertissement qui interrompent l’exécution du code VBA, nous utilisons la classe Application.

link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Ajouter et enregistrer un nouveau classeur en utilisant VBA dans Microsoft Excel 2016] | Pour ajouter et enregistrer des classeurs à l’aide de VBA, nous utilisons la classe Workbooks. Workbooks.Add ajoute facilement un nouveau classeur, cependant …​