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

*+ ENTER*

The list does not need to be sorted.

### Alternative formula #1

*+ ENTER*

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

### Alternative formula #2

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

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

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.

Thumbs up!!

I guess the columns must be sorted ascending in order for the formulas to work properly.

Thanks for your contribution!

[...] 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 [...]

happened upon this while calulating rating scores for timed surgical evaluation... needed formula to output score based upon time ranges... "formula in cell D10" worked perfectly! Thanks SO much!!!!

~charmain~,

I am happy you found it useful!

I am looking at a way to create a Bring Forward system witht the dates of the calendar.

It would be the first wednesday of the year like 2012-01-04 to 2012-01-18 would return the value of 1, and 2012-01-19 to 2012-02-01 would return the value of 2, etc...

Hi Oscar,

I came across this amazing formula when i was trying get a value return in a range:

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

But could not understand the usage of "--" in the sumproduct and also the logic behind using sumproduct itself.

Could you please help me to understand

Logical function (i.e. =46) you will always get numeric value 0. You can not convert FALSE to anything else other than numeric value 0.

It is not common to use more than two dashes since it can mess up your TRUE result (i.e. =---------------(TRUE) will return numeric value -1)

Logical function (i.e. =4 is less than 6) will return TRUE or FALSE value.

If you add one dash (-) in front of this logical function the result is the opposite (negative) value in numeric form; in this case result is -1

So if you add two dashes (--) in front of this logical function - the result is the opposite of the opposite (negative of the negative) in numeric form; in this case result is 1

The whole point is to easily convert result TRUE to numeric value 1, or convert result FALSE to numeric value 0.

p.s. of course, if you add as many dashes in front of logical function which results FALSE (i.e. =4 is more than 6) you will always get numeric value 0. You can not convert FALSE to anything else other than numeric value 0.

It is not common to use more than two dashes since it can mess up your TRUE result (i.e. =---------------(TRUE) will return numeric value -1)

Brilliant solution

I'm using the first formulation, but it assumes that the value fits between exactly 1 of the ranges. If it fits none, it returns the same value (which is not a big problem). If it fits on several ranges, it returns an error.

How do get it to provide at least one of the possible ranges instead of an error when there are several possible ranges?

Thanks!

Karl,

Array formula:

Attached file:

Return-value-if-in-range-karl.xls

Good day sir.

=INDEX(Calculate!D2:D6,SUMPRODUCT(--(Calculate!$D$9=B2:B6),ROW(Calculate!A1:A5)))

As you can see, I'm trying to put the formula to another sheet. Unfortunately I'm getting #value error.

Please advice, thank you.