filter function with manipulation of cell location

[ExcelNewbie2020]

Is filter function can rearrange the cell location? for example with the table provided. i want to filter year 2010, but i want the result, the 1st column the year, 2nd the name and 3rd the dept.

hlink.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	NAME	DEPT	YEAR			FILTER RESULT				2010
2	AA	OPS	2010			YEAR	NAME	DEPT
3	BB	SALES	2010			2010	AA	OPS
4	CC	MKTG	2012			2010	BB	SALES
5	DD	OPS	2011
6	EE	SALES	2012
7	FF	MKTG	2011
8
9
10
11
12
13
14
15
Sheet12

 

[Georgiboy]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	NAME	DEPT	YEAR			FILTER RESULT				2010
2	AA	OPS	2010			YEAR	NAME	DEPT
3	BB	SALES	2010			2010	OPS	AA
4	CC	MKTG	2012			2010	SALES	BB
5	DD	OPS	2011
6	EE	SALES	2012
7	FF	MKTG	2011
Sheet1

Cell Formulas
Range	Formula
F3:H4	F3	=CHOOSECOLS(FILTER(A2:C7,C2:C7=$J$1),3,2,1)
Dynamic array formulas.

Or maybe:

=CHOOSECOLS(FILTER(A2:C7,C2:C7=$J$1),SEQUENCE(,3,3,-1))

 

[ExcelNewbie2020]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	NAME	DEPT	YEAR			FILTER RESULT				2010
2	AA	OPS	2010			YEAR	NAME	DEPT
3	BB	SALES	2010			2010	OPS	AA
4	CC	MKTG	2012			2010	SALES	BB
5	DD	OPS	2011
6	EE	SALES	2012
7	FF	MKTG	2011
Sheet1

Cell Formulas
Range	Formula
F3:H4	F3	=CHOOSECOLS(FILTER(A2:C7,C2:C7=$J$1),3,2,1)
Dynamic array formulas.

Or maybe:

=CHOOSECOLS(FILTER(A2:C7,C2:C7=$J$1),SEQUENCE(,3,3,-1))

both worked.. thank you

 

[Peter_SSs]

.. yet another

=SORTBY(FILTER(A2:C7,C2:C7=J1),{3,2,1})