Return value if in range in excel
Question:
Hi,
What type of formula could be used if you weren't using a date range and your data was not concatenated?
ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 and Range2
Range1 Range2 Value
1.33 1.66 A
1.67 1.99 B
2.00 2.33 C
Answer:
Formula in D10:
=INDEX(D4:D6, SUMPRODUCT(--($D$8<C4:C6), --($D$8>B4:B6), ROW(A1:A3))) + ENTER
Download excel sample file for this tutorial
Return value if in range.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
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
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
Related posts:
- Count date records between two dates in a range in excel
- Return multiple values if in range in excel
- Formula for matching a date within a date range in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Lookup two index columns in excel
- Lookup a value in a list and return multiple matches in excel
- Return multiple values if above frequency criterion in excel
- Return row reference of largest to smallest
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel



January 13th, 2010 at 3:40 pm
Great post, Oscar. There are simpler formulas to do this, though.
=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)
or
=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))
will also return B.
January 13th, 2010 at 10:10 pm
Thumbs up!!
I guess the columns must be sorted ascending in order for the formulas to work properly.
Thanks for your contribution!
January 22nd, 2010 at 1:02 pm
[...] Return value if in range in excel, Oscar shows us a formula for returning values in a column based on a number range. Let's review [...]