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 cell D10:

The list does not need to be sorted.

Alternative formula #1

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

Alternative formula #2

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

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

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