Search and display a range of values in excel
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
Related posts:
Search and display all cells that contain all search strings in excel
Lookup with multiple criteria and display multiple search results using excel formula
Search for a cell in a table and then display the column title in excel
Lookup with multiple criteria and display multiple search results using excel formula, part 3
Lookup with multiple criteria and display multiple search results using excel formula, part 2
Recommended categories
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:
http://www.decisionmodels.com/calcsecretsi.htm
http://www.dailydoseofexcel.com/archives/2005/04/29/using-volatile-functions/
http://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