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 D5:
Edit: Indirect is a volatile function. See comments below. Thanks, Harlan Grove.
=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.
Formula in 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.
Named ranges
Range_text (A2:A41)
Range_num (B2:B41)
What is named ranges?
Download excel example file.
Search and display a range of values in excel.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 row 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
Related posts:
- 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 4
- Filter text values existing in range 1 but not in range 2 using array formula in excel
- Return multiple values if in range in excel
- Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
- Filter common text values in range 1 and in range 2 using array formula in excel
- Extract unique values from a range using array formula in excel
- Filter unique distinct values where adjacent cells contain search string in excel
- Lookup two index columns using min max values and a date range as criteria
August 17th, 2009 at 10:13 pm
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.
August 17th, 2009 at 10:43 pm
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
August 18th, 2009 at 10:17 am
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!