I want it all! Using single lookup command to return both multiple Rows and Columns

[OlarFin]

I have 3 worksheets:

1. "Input Data". has 5k+ rows and 20 columns.
2. "TeamInfo" has 20 rows and 5 columns.
3. "Output" processes all rows from Input and merging the TeamInfo data.

I want to do a SINGLE lookup that returns multiple rows and columns from TeamInfo.

I found that both VLookup and XLookup behave the same in this instance ( but I like XLookup better)

I want to do the command below to get rows and columns, but all I get is the 1000 rows and a single column:
XLOOKUP($A10:$A1000,TeamInfo!$A$2:$A$21,TeamInfo!$B$2:$D$21)
So it's basically doing this:
XLOOKUP($A10:$A1000,TeamInfo!$A$2:$A$21,TeamInfo!$B$2:$B$21)

If I use the command below I get one row and 3 columns (which is expected from the command)
XLOOKUP($A10,TeamInfo!$A$2:$A$21,TeamInfo!$B$2:$D$21)

My source data can fluctuate as many as 1000 rows. I don't want to do a fill down. I know I can do it with VBA but I feel I'm so close.

Goal:
I want to load the data and have the columns and rows Magically appear in my Output worksheet.

 

[Armstrong_N14]

I hope this helps!

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

 

[Fluff]

Hi & welcome to MrExcel.
How about

=DROP(REDUCE("",TOCOL(A10:A2000,1),LAMBDA(x,y,VSTACK(x,XLOOKUP(y,TeamInfo!$A$2:$A$21,TeamInfo!$B$2:$D$21,"")))),1)

 

[OlarFin]

Hi & welcome to MrExcel.
How about

=DROP(REDUCE("",TOCOL(A10:A2000,1),LAMBDA(x,y,VSTACK(x,XLOOKUP(y,TeamInfo!$A$2:$A$21,TeamInfo!$B$2:$D$21,"")))),1)

Works like a charm! This really fit's Azimov's definition of magic!
I will be breaking this incantation to figure out exactly what it is doing.
Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.