Need to extract a small range from a large one based on cell value

See if this helps.

This is your data in the range B2:C14...


Data Range

	A	B	C
1		Value	Code
2	_____	98	A
3	_____	17	A
4	_____	62	X
5	_____	51	X
6	_____	53	A
7	_____	43	A
8	_____	45	X
9	_____	27	A
10	_____	19	A
11	_____	77	X
12	_____	88	A
13	_____	29	A
14	_____	68	A

You want to extract the data from B2:B14 that corresponds to "X" in C2:C14. You want the results listed starting in cell E2 and downwards.

Enter this array formula in cell E2:

=IFERROR(INDEX(B:B,SMALL(IF(C$2:C$14="X",ROW(C$2:C$14)),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks (meaning all the data that meets the criteria has been extracted).

Your results should look like this:


Data Range

	A	B	C	D	E
1		Value	Code		Value
2	_____	98	A	_____	62
3	_____	17	A	_____	51
4	_____	62	X	_____	45
5	_____	51	X	_____	77
6	_____	53	A	_____
7	_____	43	A	_____
8	_____	45	X	_____
9	_____	27	A	_____
10	_____	19	A	_____
11	_____	77	X	_____
12	_____	88	A	_____
13	_____	29	A	_____
14	_____	68	A	_____