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:

1. Count date records between two dates in a range in excel
2. Return multiple values if in range in excel
3. Formula for matching a date within a date range in excel
4. Lookup values in a range using two or more criteria and return multiple matches in excel
5. Lookup two index columns in excel
6. Lookup a value in a list and return multiple matches in excel
7. Return multiple values if above frequency criterion in excel
8. Return row reference of largest to smallest
9. Vlookup with 2 or more lookup criteria and return multiple matches in excel
10. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel