Array formula in D12 (cell references):

=INDEX($D$4:$D$8, SMALL(IF(($B$4:$B$8<$D$10)*($C$4:$C$8>$D$10), ROW($D$4:$D$8)-MIN(ROW($D$4:$D$8))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in D12 (named ranges):

=INDEX(Value_col, SMALL(IF((Rng_1<Inp_val)*(Rng_2>Inp_val), ROW(Value_col)-MIN(ROW(Value_col))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges

Value_col (D4:D8)
Rng_1 (B4:B8)
Rng_2 (C4:C8)
What is named ranges?

Download excel sample file for this tutorial

Return multiple values if in range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

Related posts:

1. Lookup values in a range using two or more criteria and return multiple matches in excel
2. How to return multiple values using vlookup in excel
3. Return multiple values if above frequency criterion in excel
4. Vlookup with 2 or more lookup criteria and return multiple matches in excel
5. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
6. Match two criteria and return multiple rows in excel
7. Lookup a value in a list and return multiple matches in excel
8. Filter text values existing in range 1 but not in range 2 using array formula in excel
9. Filter values existing in range 1 but not in range 2 using array formula in excel
10. Sum adjacent values using multiple lookup text values in a column in excel