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

  • Share/Bookmark

Related posts:

  1. Search and display all cells that contain all search strings in excel
  2. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  3. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  4. Lookup with multiple criteria and display multiple search results using excel formula
  5. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  6. Filter values existing in range 1 but not in range 2 using array formula in excel
  7. Filter text values existing in range 1 but not in range 2 using array formula in excel
  8. Return multiple values if in range in excel
  9. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
  10. Search for multiple text strings in multiple cells in excel