Returning 6 columns of data from a second table from one key lookup.

[Brboland]

I have two tables. One is my data table where I want to retrieve certain columns based on my first table where I will enter one field to lookup. This will be a table of roughly 300 rows. The table where I want to pull the data from is roughly 9K rows. What would be the best formula to use in this case? I have one workbook with two worksheets/tables.

 

[Brboland]

As you are putting that into a table, you cannot use spill ranges, so you will need to use xlookup in each column to retrieve the data you want.
Are you happy to convert the table with the formula into a normal range?

Als0 the project Id column has nothing that resembles your lookup value of 20211

Thank you for the response. The data of "20211" is an example where the lookup range I should have displayed should have started with 20211. Each time I try using INDEX, MATCH, and CHOOSE I get the SPILL error. I appreciate your suggestions.

 

[Fluff]

Can you answer my other question

Are you happy to convert the table with the formula into a normal range?

 

[Brboland]

Can you answer my other question

Currently I'm using Power Query to retrieve the data to trim it down to manageable size. Do you have any suggestions?

 

[Fluff]

That still does not answer my question regarding the table that will contain the formula.

 

[Brboland]

That still does not answer my question regarding the table that will contain the formula.

Yes, I'm open to converting the table where the formula will be.

 

[Fluff]

In that case you use something like

=XLOOKUP(A2,Tbl_Data[Project ID],CHOOSECOLS(Tbl_Data[[Name]:[Cust Level 1]],1,4,12))