Renvoyer des lignes ou des colonnes entières

Jusqu’à présent, XLOOKUP renvoyait une seule valeur. Mais il peut également renvoyer une ligne ou une colonne entière de données en une seule fois.

Lorsqu’une formule renvoie plusieurs valeurs, celles-ci se déversent automatiquement dans les cellules voisines. Ainsi, un seul XLOOKUP peut remplir une fiche entière.

L’astuce consiste à faire en sorte que le tableau de renvoi s’étende sur plusieurs colonnes. Au lieu de renvoyer uniquement B2:B20, renvoyez B2:E20.

XLOOKUP trouve la ligne correspondante, puis renvoie toutes les colonnes de cette ligne dans le tableau de renvoi. Une formule, quatre résultats.

Saisissez la formule dans une seule cellule, par exemple F2, puis appuyez sur Entrée. Les valeurs apparaissent dans F2, G2, H2 et I2.

Une fine bordure bleue délimite la plage de déversement. Vous ne modifiez que la cellule supérieure gauche ; les autres sont remplies par le déversement et ne peuvent pas être modifiées directement.

Un tableau des employés contient l’ID dans la colonne A, puis le nom, le service, le poste et le salaire dans les colonnes B à E.

Saisissez un ID dans D2 et un seul XLOOKUP renvoie l’enregistrement complet. Modifiez l’ID et toute la ligne se met instantanément à jour : un petit outil de recherche dans une seule formule.

La même idée fonctionne verticalement. Si vous effectuez une recherche dans une ligne d’en-têtes, vous pouvez renvoyer une colonne entière de résultats.

Ici, XLOOKUP recherche un libellé dans la ligne d’en-têtes B1:E1 et déverse la colonne complète B2:E50 sous l’en-tête correspondant.

Une fois qu’une formule se déverse, vous pouvez faire référence à la plage déversée entière en utilisant la cellule suivie d’un signe dièse, comme F2#.

C’est puissant : une fonction SUM appliquée à une ligne déversée reste correcte même si la largeur de la ligne change, car F2# signifie toujours « tout le déversement à partir de F2 ».

Comme le résultat est un tableau, vous pouvez le transmettre directement à des fonctions qui acceptent des plages.

Par exemple, intégrez la recherche dans SUM pour totaliser une ligne renvoyée de chiffres mensuels, le tout dans une seule formule et sans cellules auxiliaires.

Une formule qui se déverse a besoin de cellules vides pour se remplir. Si une cellule de la zone de déversement contient déjà des données, XLOOKUP renvoie une erreur #SPILL!.

La solution est simple : effacez les cellules qui bloquent le déversement ou déplacez la formule dans une zone libre. La plage de déversement doit être entièrement vide.

Pour créer une carte de recherche soignée, vous pouvez également déverser les en-têtes des champs.

Placez un XLOOKUP qui renvoie la ligne de données et faites référence à la plage d’en-têtes au-dessus. Lorsque le déversement s’élargit ou se réduit, vos libellés restent alignés sur les colonnes renvoyées.

Vous pouvez même imbriquer un XLOOKUP dans un autre. Le premier renvoie une colonne entière et le second en sélectionne une seule cellule.

Vous obtenez ainsi une véritable recherche à deux dimensions, qui fait correspondre à la fois une ligne et une colonne, entièrement avec XLOOKUP. C’est une alternative élégante à INDEX-MATCH-MATCH.

Vous savez désormais que XLOOKUP peut renvoyer plusieurs valeurs :

• Un tableau de renvoi à plusieurs colonnes déverse une ligne entière
• Un tableau de renvoi à plusieurs lignes déverse une colonne entière
• Référencez le déversement avec le suffixe #
• Effacez les cellules qui bloquent le déversement pour éviter #SPILL!

Une seule formule devient ainsi un outil complet d’affichage d’enregistrements.

Vérifiez votre compréhension des résultats déversés par XLOOKUP.

Vous avez terminé le cours sur XLOOKUP en apprenant à déverser les résultats :

• Élargissez le tableau de renvoi pour déverser une ligne ou une colonne entière
• Les déversements remplissent les cellules voisines vides et affichent une bordure bleue
• Faites référence à un déversement avec le suffixe #, comme F2#
• Évitez #SPILL! en gardant la zone de déversement libre

Une fois la syntaxe, les valeurs de remplacement, la direction et le déversement maîtrisés, XLOOKUP peut remplacer presque toutes les anciennes recherches que vous écrivez.