Pull values from an array that satisfy criteria

[ptridimas]

Hallo everyone

In the table below there are 2 columns. The fist specifies the year and the second some values. I would like to know whether is a way by using a function to pull all data from column B that concern e,g, year 2018, and show them in a column somewhere else in the sheet.

Year	Values
2018	Value 01
2018	Value 02
2018	Value 03
2018	Value 04
2018	Value 05
2018	Value 06
2019	Value 07
2019	Value 08
2019	Value 09
2020	Value 10
2020	Value 11
2020	Value 12
2020	Value 13

Thanks in advance

 

[Legacy 143009]

Paste to C2 and pull down:

=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=2018,ROW($A$1:$A$100)),ROWS($A$1:$A1)))

This is an array formula. Hitting Enter only is not enough. Press Ctrl+Shift+Enter together after paste.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here are two possibilities depending on your Excel version.
H1 needs to be copied down, F1 does not. Neither require Ctrl+Shift+Enter confirmation.

23 01 20.xlsm
	A	B	C	D	E	F	G	H
1	Year	Values		Year		Value 01		Value 01
2	2018	Value 01		2018		Value 02		Value 02
3	2018	Value 02				Value 03		Value 03
4	2018	Value 03				Value 04		Value 04
5	2018	Value 04				Value 05		Value 05
6	2018	Value 05				Value 06		Value 06
7	2018	Value 06
8	2019	Value 07
9	2019	Value 08
10	2019	Value 09
11	2020	Value 10
12	2020	Value 11
13	2020	Value 12
14	2020	Value 13
Year Data

Cell Formulas
Range	Formula
F1:F6	F1	=FILTER(B2:B14,A2:A14=D2,"")
H1:H14	H1	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$14)/(A$2:A$14=D$2),ROWS(H$1:H1))),"")
Dynamic array formulas.

 

[ptridimas]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here are two possibilities depending on your Excel version.
H1 needs to be copied down, F1 does not. Neither require Ctrl+Shift+Enter confirmation.

23 01 20.xlsm
	A	B	C	D	E	F	G	H
1	Year	Values		Year		Value 01		Value 01
2	2018	Value 01		2018		Value 02		Value 02
3	2018	Value 02				Value 03		Value 03
4	2018	Value 03				Value 04		Value 04
5	2018	Value 04				Value 05		Value 05
6	2018	Value 05				Value 06		Value 06
7	2018	Value 06
8	2019	Value 07
9	2019	Value 08
10	2019	Value 09
11	2020	Value 10
12	2020	Value 11
13	2020	Value 12
14	2020	Value 13
Year Data

Cell Formulas
Range	Formula
F1:F6	F1	=FILTER(B2:B14,A2:A14=D2,"")
H1:H14	H1	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$14)/(A$2:A$14=D$2),ROWS(H$1:H1))),"")
Dynamic array formulas.

Thank you very much, the filter function worked perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
.. and for updating your details.