EXCEL problem -> Function that searches for correct column based on headline and returns specific cells if they match values listed on list

I am working with a relatively large dataset, which has been extracted from MIKE+ and into Excel. The real dataset is about pipes, where I need to return all the pipes that are the same materials.

However, the order of which the columns will appear can change, as well as the random apearens of blank columns within my table.

I therefore need a function, that can search for the relevant column in the raw data, based on the headline of its own column, and then extract all the cells from that column, which appear on the configuration list.

I have made simplified tables below.

The raw data

	A	B	C
1	ID	Number	Placement
2	Cow	1	a
3	Horse	2	b
4	Dog	3	c
5	Fish	4	d

But It could also look like this

	A	B	C
1	Number	ID	Placement
2	3	Dog	c
3	4	Fish	d
4	1	Cow	a
5	2	Horse	b

ID configuration

	D
1	Placement
2	b
3	d

My desired result for the column A

	A
1	ID
2	Horse
3	Fish

I have managed to write a function for ID, that finds the correct ID based on the configuration list, but this function only work when I specify the correct column:

=FILTER(A$1:A$18; ISNUMBER(MATCH(C$1:C$18; D$2:D$9; 0)))

Moreover, I have a function that I use for the Number and Placement columns, which finds the correct cell based on the headline. However, this does not take into account the configuration list but instead the ID value, which for these two columns is excactly what it should do:

=IFERROR(
    XLOOKUP(
        AH2#;
        INDEX(AC1:AE7; 0; MATCH($AH$1; AC1:AE1; 0));
        INDEX(AC1:AE7; 0; MATCH(AK$1; AC1:AE1; 0))
    );
    ""
)

I have not been able to combine the two functions, so my first column (the ID column) can be generated from the column in the raw table with the same headline, and the cells that are returned are those that also appear on the configuration list.

The function cannot be a drag down function, a vba function or require copy+paste steps.

Hope you can help me fix the function for the first column, which I can base all the functions in the other columns on.

Solution

Assuming no Excel Constraints then the following formula should work as per the comments by user:

=CHOOSECOLS(FILTER(A3:C8,
 MMULT(1 - ISNA(XMATCH(A3:C8,E3:E5)),{1;1;1})),
 XMATCH(F2:H2, A2:C2))

Alternatively, for multiple columns change the {1;1;1} using SEQUENCE() function:

=CHOOSECOLS(FILTER(A3:C8,
 MMULT(1-ISNA(XMATCH(A3:C8,E3:E5)),
 SEQUENCE(COLUMNS(F2:H2))^0)),
 XMATCH(F2:H2,A2:C2))

To explain on above:

XMATCH() is used to match each of those in Configuration List with the actual source, if found it returns the relative position else returns an error as #N/A
Using ISNA() function to check whether the array returned using above is error or not, if error returns TRUE else FALSE.
Using 1-ISNA() is to just reverse the TRUE to FALSE i.e. 0 while the FALSE to TRUE i.e. 1 two above methods can be done using one single function called ISNUMBER() however to make it shorter I have used method.
Using MMULT() function to perform a matrix multiplication of the resulting array from above with the array constant {1;1;1} which is actually meant for the three columns, and can be made dynamic using SEQUENCE() and COLUMNS() function( which i did in the second method to show).
Next, using FILTER() function to include and returns only those which have returned a non-zero value.
Lastly, using CHOOSECOLS() and XMATCH() function to return the output as based on selected column on the matches found.