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.

 

[Fluff]

Sounds like you want to use XLOOKUP XLOOKUP Function

 

[Brboland]

Sounds like you want to use XLOOKUP XLOOKUP Function

I find that XLOOKUP works when I need to retrieve one column per formula/field. What I need to do is retrieve 6 columns of data based on one key lookup field. Any suggestions?

 

[Fluff]

Yes use Xlookup, it can return multiple columns.

If you need further help then please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Bedlam]

You want the Filter function then in conjunction with the ChooseCols function

=CHOOSECOLS(FILTER($A$3:$F$33,$A$3:$A$33=H4,"-"),1,2,3)

 

[Fluff]

Why use choosecols to return the 1st 3 columns? when you can just use

=FILTER($A$3:$C$33,$A$3:$A$33=H4,"-")

 

[Bedlam]

It's an example. We have not heard if the columns are consecutive or if they need to be pulled in a certain order

 

[Brboland]

Yes use Xlookup, it can return multiple columns.

If you need further help then please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Brboland]

Here are images of the sheets in question. Sheet one is where I want to enter one field to lookup and populate column B - G. Second sheet is my data where I want to get information from. I'm trying to retrieve columns B, E, M, Q, AH, AK

 

[Fluff]

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