## Search and display a range of values in excel

*Article updated on August 03, 2011*

**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:**

**Formula in cell D5:**

=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:**

=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

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

- Press and hold Ctrl + Shift.
- Press Enter once.
- 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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 17 Responses to “Search and display a range of values in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Your approach uses volatile functions. They're unnecessary for this. As long as Range_num contains no duplicates, it's sufficient to use

E5 [array formula]:

=INDEX(Range_num,MATCH(TRUE,

ABS(Range_num-(from+to)/2)<=(to-from)/2,0))

D5:

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

E6 [array formula]:

=INDEX(Range_num,MATCH(E5,Range_num,0)+MATCH(TRUE,

(ABS(INDEX(Range_num,MATCH(E5,Range_num,0)+1)

:INDEX(Range_num,ROWS(Range_num))-(from+to)/2)<=(to-from)/2),0))

E6:

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

And fill D6:E6 down as far as needed. There's also no good reason at all to use the same complicated index expression for the col D values as for the col E values.

This sort of thing is best left in the newsgroups where criticism is quick and sometimes scathing. Poor methodology needs to be exposed for what it is.

Thank you for your valuable comment!

I learned a lot here!

Volatile functions:

https://www.decisionmodels.com/calcsecretsi.htm

https://www.dailydoseofexcel.com/archives/2005/04/29/using-volatile-functions/

https://www.ozgrid.com/News/GoodVsBadDesignSpeedUpEvents.htm

I really appreciate your comment!

I did not know about the volatile INDIRECT function. Your comment helps me create a better website. I will change all previous blog articles containing volatile functions as soon as I have a better formula.

Thanks again!

This does not seem to operate when a decimal place is added. Changing a value from 23 to 23.1 creates errors. Can this be corrected?

Evan Galvanek,

I believe this should work as long as Range_num contains no duplicates:

Download excel example file.Search-and-display-a-range-of-values-in-excel2.xls

(Excel 97-2003 Workbook *.xls)

or read the new updated formulas in this post.

Thanks Oscar. I downloaded the 2nd excel file. Works well with decimals. Is there a way to list the array without encountering any #NUM! errors?

Evan Galvanek,

Sure!

Array formula

=IFERROR(formula, "")

As an aside, your solution to list numbers within a range will be used in some of my mass spectrometry work. I have designed a "charge-state" calculator which lists the theoretical masses of a peptide when analysed by the instrument. There are times when these masses are not probable / not necessary which is where the number range function comes into play. The end result is a list of comma separated values used to search for masses within our Mass spectrometry software, MassLynx. That string is generated like this:

=J6&","&J7&","&J8&","&J9&","&J10&","&J11&","&J12&","&J13

where J# is the results from your ranged list. I thought it might be nice for you to see the value in your efforts.

Thanks!

Could you walk me through incorporating that function to the existing worksheet?

I think the IFERROR function is only for 2007. I am working in Excel 2003.

Figured it out. I used

=ISERROR(A#)

then

=IF(A#,"",Relevant cell)

Thanks for all your help and direction.

Evan Galvanek,

Thanks for describing how you use the number range function. I am happy you figured it out in excel 2003.

I am interested to find out how you would handle it if Range_num did contain duplicates?

Matthew

Hi

I tried the following

=SMALL(IF((Range_num>=from)*(Range_num=from_dt)*(Range_expiry<=to_dt), Range_product, ""), ROW(A1))

Kindly let me know if there is any alternate for dates.

Thank you

Reginald

Reginald,

Check out this page:

Filter records within two dates and search for a text string in excel

hi

i need a solution for my work....i have been given a data..i need to write code to a command button. once the button is pressed the user should b asked for a range..the values corresponding to the range given from the user has to b displayed...

help is needed and please....

jeff

Hi, Being a Payable Accountant, I have this following doubt.

I want excel to automatically pick the "check value" or amounts paid to Supplier month-wise.To make it easy to understand, I have all the details like amount, check#, Month(check paid for respective month) etc in one excel sheet(eg Sheet 1), in another sheet(eg Sheet 2), I want to know at a glance, for which months I have paid the supplier(age-wise details), and which months are pending-I want to create a link from Sheet1 to Sheet 2.

Can you help me please? Liju