Forum Discussion
Extract a subset of a table in another sheet (and keep the link to the master table)
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)
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)