Question: How do I search and display a range of values, if the values were numerical, say 1 - 40 could I set a range of 20 - 25 (either in one or 2 cells) and get 6 values (inclusive)?

Answer:

Search-and-display-a-range-of-values-in-excel

Formula in cell D5:

=INDEX(Range_text, MATCH(E5, Range_num, 0))

=INDEX(Range_text, SMALL(IF(TRANSPOSE(ROW(INDIRECT($F$1&":"&$F$2)))=Range_num, ROW(Range_num)-MIN(ROW(Range_num))+1, ""), ROWS(D4:$D$4))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Array Formula in cell E5:

=SMALL(IF((Range_num>=from)*(Range_num<=to), Range_num, ""), ROW(A1))

=INDEX(Range_num, SMALL(IF(TRANSPOSE(ROW(INDIRECT($F$1&":"&$F$2)))=Range_num, ROW(Range_num)-MIN(ROW(Range_num))+1, ""), ROWS(D4:$D$4))) + CTRL + SHIFT + ENTER copied down as far as necessary.

Edit: Indirect is a volatile function. See comments below. Thanks, Harlan Grove.

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Named ranges

from (F1)
to (F2)
Range_text (A2:A41)
Range_num (B2:B41)
What is named ranges?

Explaining array formula in cell E5

Step 1 - Filter values larger or equal to 20

=SMALL(IF((Range_num>=from)*(Range_num<=to), Range_num, ""), ROW(A1))

Range_num>=from

becomes

{18; 39; 24; 32; 11; 4; 29; 3; 25; 31; 5; 15; 30; 34; 40; 13; 14; 28; 8; 1; 36; 10; 2; 9; 19; 6; 23; 7; 27; 37; 35; 21; 33; 12; 17; 26; 16; 20; 22; 38} >= 20

and returns this array

{FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE}

Step 2 - Filter values smaller or equal to 25

=SMALL(IF((Range_num>=from)*(Range_num<=to), Range_num, ""), ROW(A1))

Range_num<=to

becomes

{18; 39; 24; 32; 11; 4; 29; 3; 25; 31; 5; 15; 30; 34; 40; 13; 14; 28; 8; 1; 36; 10; 2; 9; 19; 6; 23; 7; 27; 37; 35; 21; 33; 12; 17; 26; 16; 20; 22; 38} <= 25

and returns this array

{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE}

Step 3 - Check whether a condition is met, and return an array of values if TRUE

=SMALL(IF((Range_num>=from)*(Range_num<=to), Range_num, ""), ROW(A1))

IF((Range_num>=from)*(Range_num<=to), Range_num, "")

becomes

IF(({FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE})*({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE}), Range_num, "")

becomes

IF({0; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 1; 1; 0}, {18; 39; 24; 32; 11; 4; 29; 3; 25; 31; 5; 15; 30; 34; 40; 13; 14; 28; 8; 1; 36; 10; 2; 9; 19; 6; 23; 7; 27; 37; 35; 21; 33; 12; 17; 26; 16; 20; 22; 38} , "")

and returns this array

{""; ""; 24; ""; ""; ""; ""; ""; 25; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 23; ""; ""; ""; ""; 21; ""; ""; ""; ""; ""; 20; 22; ""}

Step 4 -  Return the k-th smallest number in this data set

=SMALL(IF((Range_num>=from)*(Range_num<=to), Range_num, ""), ROW(A1))

becomes

=SMALL({""; ""; 24; ""; ""; ""; ""; ""; 25; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 23; ""; ""; ""; ""; 21; ""; ""; ""; ""; ""; 20; 22; ""}, ROW(A1))

becomes

=SMALL({""; ""; 24; ""; ""; ""; ""; ""; 25; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 23; ""; ""; ""; ""; 21; ""; ""; ""; ""; ""; 20; 22; ""}, 1)

and returns 20 in cell E5.

Explaining formula in cell D5

Step 1 - Return the relative position of an item in an array that matches a specified value

=INDEX(Range_text, MATCH(E5, Range_num, 0))

MATCH(E5, Range_num, 0)

becomes

MATCH(20, {18; 39; 24; 32; 11; 4; 29; 3; 25; 31; 5; 15; 30; 34; 40; 13; 14; 28; 8; 1; 36; 10; 2; 9; 19; 6; 23; 7; 27; 37; 35; 21; 33; 12; 17; 26; 16; 20; 22; 38}, 0)

and returns 38.

Step 2 - Return a value of the cell at the intersection of a particular row and column, in a given range

=INDEX(Range_text, MATCH(E5, Range_num, 0))

becomes

=INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "X"; "Y"; "Z"; "AA"; "BB"; "CC"; "DD"; "EE"; "FF"; "GG"; "HH"; "II"; "JJ"; "KK"; "LL"; "MM"; "NN"; "OO"}, 38)

and returns "MM" in cell D5.

Download excel example file.
Search-and-display-a-range-of-values-in-excel2.xls
(Excel 97-2003 Workbook *.xls)

I created a random unique list (1-40) in B2:B41 (see above picture) using the array formula in this post: How to create a random unique list of numbers in excel

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

ROW(reference) returns the rownumber of a reference

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

TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value