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

### Formula in cell D10:

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3))) + ENTER

The list does not need to be sorted.

### Alternative formula #1

=VLOOKUP(\$D\$8,\$B\$4:\$D\$6,3,TRUE) + ENTER

The list must be sorted, otherwise this formula won´t work.

### Alternative formula #2

=INDEX(\$D\$4:\$D\$6,MATCH(D8,\$B\$4:\$B\$6,1)) + ENTER

The list must be sorted, otherwise this formula won´t work.

Thanks JP!

### Explaining formula in cell D10:

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

Step 1 - Calculate first criterion

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

Step 1 - Calculate second criterion

--(\$D\$8<=C4:C6)

becomes

--(1,78<={1,66;1,99;2,33})

becomes

--({FALSE;TRUE;TRUE})

and returns

{0;1;1}

Step 2 - Calculate second criterion

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

--(\$D\$8>=B4:B6)

becomes

--(1,78>={1,33;1,67;2})

becomes

--({TRUE;TRUE;FALSE})

and returns

{1;1;0}

Step 3 - Create row numbers

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

ROW(A1:A3)

returns

{1;2;3}

Step 4 - Multiply criteria and row numbers and sum values

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3))

becomes

SUMPRODUCT({0;1;1}, {1;1;0}, {1;2;3})

and returns number 2.

Step 5 - Return a value of the cell at the intersection of a particular row and column

=INDEX(D4:D6, SUMPRODUCT(--(\$D\$8<=C4:C6), --(\$D\$8>=B4:B6), ROW(A1:A3)))

becomes

=INDEX(D4:D6, 2)

becomes

=INDEX({"A";"B";"C"}, 2) ﻿﻿

and returns "B".

### Explaining alternative formula #1 in cell D10:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.

range_lookup is optional. range_lookup is either TRUE (or omitted) or FALSE . TRUE: exact match or an approximate match. If an exact match is not found, the next largest value that is less than lookup_value is returned. The table_array must be placed in ascending sort order.

If range_lookup is FALSE the table_array does not need to be sorted.

=VLOOKUP(\$D\$8,\$B\$4:\$D\$6,3,TRUE)

becomes

=VLOOKUP(1,78,{1,33, 1,66, "A";1,67, 1,99, "B";2, 2,33, "C"},3,TRUE)

1,67 is the next largest value and the VLOOKUP function returns "B".

### Explaining alternative formula #2 in cell D10:

=INDEX(\$D\$4:\$D\$6,MATCH(D8,\$B\$4:\$B\$6,1))

Step 1 - Return the relative position of an item in an array

MATCH(lookup_value,lookup_array, [match_type]) returns the relative position of an item in an array that matches a specified value

[match_type] is optional. The number can be -1, 0, or 1. 1 is default.  The match_type argument specifies how Excel matches lookup_value with values in lookup_array.

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order.

=INDEX(\$D\$4:\$D\$6,MATCH(D8,\$B\$4:\$B\$6,1))

MATCH(D8,\$B\$4:\$B\$6,1)

becomes

MATCH(1.78,{1.33;1.67;2},1)

1.67 is the largest value that is less than or equal to lookup_value. 1.67 is the second value in the array. MATCH function returns 2.

Step 2 - Return a value of the cell at the intersection of a particular row and column

=INDEX(\$D\$4:\$D\$6,MATCH(D8,\$B\$4:\$B\$6,1))

becomes

=INDEX(\$D\$4:\$D\$6,2)

becomes

=INDEX({"A";"B";"C"},2)

and returns "B".

Return-value-if-in-range.xls
(Excel 97-2003 Workbook *.xls)