Extract a subset of a table in another sheet (and keep the link to the master table)

[sintx]

Let me start by saying I am working on a Mac with excel 365 and am challenged by the following problem.

I have a large master table or dated transactions that I append to periodically. The table has hard entries  and calculated columns. 

I am trying to extract a subset of the table into a new table on a separate worksheet based on the values in a specific column and a subset of columns from the master table (non sequential) in a dynamic way. currently I filter the master and then copy the data for the app, time consuming and prone to errors.

I have tried with =XLOOKUP($G$1,'Master Import'!$K$2:$K$9254,'Master Import'!$A$2:$Q$9254) and thought I could use it as an array formula... no success. actually would like the return data to be select columns not all the columns.

I also tried to make something with matcha nd index but don't really know what I am doing.

[#2]

@sintx Perhaps your Excel version already supports the new FILTER function. It spills the subset from a larger table based on criteria set in the formula. Basically the same as the filtering, copying, pasteing that you do now, but it's all dynamic and will update automatically with the larger table. 

[#3 · Accepted solution]

@Riny_van_Eekelen 

 Thank you very much, Indeed the Filter function is working well.

It does seem to require that the Filter rule needs to be related to a cell reference. ( cannot be a text entered directly)

An additional question is with reference to the below formula, I now have multiple filter formula's to extract the specific column or consecutive columns. Is there a way to either have a listing of desired columns in the array or a formula that builds the filter function, making it dynamic?

=FILTER('Master Import'!$B$2:$B$9456,'Master Import'!$F$2:$F$9456=$B$2)

[#4]

@sintx Please clarify. Don't really understand either of your questions.

[#5]

@sintx 

Firstly, XLOOKUP will not return a 2D spilt array.  It will either match an array of 'foreign keys' but for a single column only OR it will return an entire record (as a range reference) but for a single key (the relative reference operator @ will pick the foreign keys one by one when filled down.

A fixed set of column results can be stitched together using CHOOSE but the columns are then not dynamic.  

= CHOOSE({1,2,3},
  XLOOKUP(fKey,pKey,Data[Column2]),
  XLOOKUP(fKey,pKey,Data[Column6]),
  XLOOKUP(fKey,pKey,Data[Column3]) )

Alternatively, INDEX/XMATCH will return a 2D array as you suggest.

= INDEX(Data,XMATCH(fKey,pKey),selectCol)

As @Riny_van_Eekelen suggests, FILTER is also effective, but building the filter criteria may not be straightforward (a COUNTIFS to test each primary key to see whether it is present in the list of foreign keys)