Comment effectuer une recherche bidirectionnelle dans Excel avec xlookup

La fonction XLookup de Microsoft Excel est surtout connue pour récupérer une valeur d'une certaine ligne ou colonne dans un ensemble de données. Cependant, vous pouvez également utiliser cet outil puissant pour effectuer des recherches bidirectionnelles, renvoyant une valeur à l'intersection d'une ligne et d'une colonne spécifiées.

Si vous travaillez dans l'application Excel Desktop sur un PC ou un Mac, vous devez utiliser Excel 2021 ou ultérieure (y compris Excel pour Microsoft 365) pour accéder à la fonction XLookup. Il est également facilement disponible dans Excel pour le Web et la tablette Excel et les applications mobiles.

Utilisation de xlookup pour des recherches à sens unique dans Excel

La clé pour comprendre comment Xlookup peut être utilisé pour effectuer des recherches bidirectionnelles est d'abord de faire en sorte que cette fonction fonctionne dans les scénarios unidimensionnels.

La fonction xlookup a six arguments:

=XLOOKUP(a,b,c,d,e,f)

où

• un (requis) est la valeur de recherche,
• b (requis) est le tableau de recherche,
• c (requis) est le tableau de retour,
• d (Facultatif) est le texte à retourner si la valeur de recherche (un) ne se trouve pas dans le tableau de recherche (b),
• e (Facultatif) est le mode de correspondance (0 = correspondance exacte; -1 = correspondance exacte ou un élément plus petit suivant; 1 = correspondance exacte ou un élément plus grand suivant; 2 = correspondance de wildcard), et
• f (Facultatif) est le mode de recherche (1 = de haut en bas ou de gauche à droite; -1 en bas en haut ou à droite à gauche; 2 = recherche binaire avec le tableau de recherche (b) dans l'ordre croissant; -2 = recherche binaire avec le tableau de recherche (b) par ordre croissant.

Si l'argument d est omis, Excel renvoie zéro si la valeur de recherche n'est pas trouvée dans le tableau de recherche. Si les arguments e et f sont laissés de côté, l'option par défaut est appliquée (une correspondance exacte pour l'argument eet une recherche de haut en bas ou de gauche à droite de l'argument f).

Dans cet exemple, taper:

=XLOOKUP(F2,A2:A100,D2:D100,"Invalid ID",0,1)

dans Cell G2 renvoie un score de 48.

Cette formule prend l'ID dans la cellule F2 (argument un), le cherche dans les cellules A2 à A100 (argument b), et renvoie la valeur correspondante dans les cellules D2 à D100 (argument c). Si l'ID dans la cellule F2 n'est pas répertorié dans les cellules A2 à A100, la recherche renvoie le terme « ID non valide » (argument d). Le cinquième argument dit à Excel de renvoyer les matchs exacts uniquement (argument e), et le sixième garantit que la recherche fonctionne du haut de la plage au bas de la plage (argument f).

Utilisation de xlookup pour des recherches bidirectionnelles dans Excel

Bien que les recherches à sens unique soient utiles dans de nombreux scénarios, ils ne vous permettent pas de modifier la variable que vous recherchez. En effet, la formule ci-dessus ne renvoie qu'un score basé sur l'ID spécifié, mais que se passe-t-il si vous vouliez voir l'âge ou le sexe à la place? C'est là qu'une recherche bidirectionnelle (également connue sous le nom de recherche bidimensionnelle ou matricielle) entre en jeu.

Pour ce faire, vous devez nicher un xlookup à l'intérieur de l'autre:

=XLOOKUP(a¹,b¹,XLOOKUP(a²,b²,c))

où

• a´ et B¹ sont la valeur de recherche et le tableau pour la première variable,
• a² et b² sont la valeur de recherche et le tableau pour la deuxième variable, et
• c est le tableau de retour.

Vous pouvez également ajouter des arguments d, eet f Pour chaque xlookup exactement de la même manière que vous le feriez lors de l'exécution d'une recherche unidirectionnelle. Cependant, pour garder les choses simples, je les ai omises de la syntaxe ci-dessus et des exemples ci-dessous.

Sur la base de cela, tapant:

=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))

dans Cell G2 renvoie un score de 48.

En effet, Excel recherche d'abord l'ID spécifié (argument a´) Dans les cellules A2 à A100 (argument B¹), puis il recherche la variable spécifiée (argument a²) dans les cellules B1 à D1 (argument b²), et renvoie le résultat à l'intersection de ces deux recherches dans les cellules B2 à D100 (argument c).

En conséquence, si vous modifiez la variable dans Cell G1 en «âge», vous obtenez le résultat de 41.

De même, si vous le modifiez en «genre», vous obtenez m en conséquence.

Dans les formules XOVEUP bidimensionnelles, peu importe dans quelle mesure les Xookups gèrent. Dans cet exemple, la recherche d'identification est la première dans la formule et la recherche d'en-tête de colonne est imbriquée. Si ceux-ci étaient commutés, vous obtiendrez le même résultat.

Conseil de pro: ajouter des listes déroulantes aux cellules de recherche

Maintenant que votre recherche bidirectionnelle est terminée, vous pouvez aller plus loin en ajoutant une liste déroulante aux cellules contenant les valeurs de recherche pour accélérer le processus de récupération des données.

Pour ce faire, sélectionnez l'une des cellules de recherche (dans ce cas, Cell G1), et dans l'onglet Données du ruban, cliquez sur « Validation des données ».

Lorsque vous cliquez sur « OK », vous verrez un bouton déroulant dans Cell G1 qui, une fois cliqué, révèle les options de variable.

Maintenant, répétez le processus pour la cellule F2, l'autre cellule de recherche.

Alternativement, vous pouvez utiliser une formule qui compte le nombre de cellules contenant des valeurs dans la colonne A et renvoie ce nombre d'options dans le menu déroulant.

Maintenant, vous pouvez sélectionner un ID dans la liste déroulante dans Cell F2.

---

Vous pouvez également effectuer des recherches bidirectionnelles dans Excel en utilisant l'index et la correspondance. Cette option alternative est particulièrement utile si vous utilisez Excel 2019 ou plus, car ces fonctions ont été introduites au programme avant xlookup.