## How to return a value if lookup value is in range

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

**Quickly lookup a value in a numerical range**

You can also do lookups in date ranges, dates in Excel are actually numbers.

### LOOKUP function

The easiest way to go is the LOOKUP function, watch a video below to see how it works:

The picture below shows you data in cell range B3:C6, the search value is in C8 and the result is in C10.

Cell range B4:B6 must be sorted in ascending order for the LOOKUP function to work properly if an exact match is not found the largest value is returned as long as it is smaller than the lookup value. The LOOKUP function then returns a value in a column on the same row.

Example, Search value 1.71 has no exact match, the largest value that is smaller than 1.71 is 1.67. The returning value is found in column C on the same row as 1.67, in this case, B.

If the search value is smaller than the smallest value in the lookup range the function returns #N/A meaning Not Available or does not exist. Example in above picture, search value is 1 in the and the LOOKUP function returns #N/A.

A search value greater than the largest value in the lookup range matches the largest value. Example in above picture, search value is 3 and the returning value is C.

**The formula in cell C10:**

Learn more about the LOOKUP function, recommended reading:

Finds a value in a sorted cell range and returns a value on the same row.

#### Tip! - You can quickly sort a cell range, follow these steps:

- Right-click on a cell in the cell range you want to sort

- Hover with mouse cursor over Sort
- Click on "Sort Smallest to Largest"

### INDEX + SUMPRODUCT + ROW

The following formula is slightly larger but you don't need to sort cell range B4:B6.

**The formula in cell C11:**

The ranges don't need to be sorted however you need a start (Range1) and an end value (Range2).

*Recommended reading*

**Return linear value if in range**

This post explains how to return a linear value depending on lookup value and range

**Explaining formula in cell C11**

You can easily follow along, go to tab "Formulas" and click "Evaluate Formula" button. Click "Evaluate" button to move to next step.

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

Functions in this formula: INDEX, SUMPRODUCT, ROW

**VLOOKUP function**

The VLOOKUP function requires your ranges to be sorted like the LOOKUP function above, the advantage is that you need only Range1 column.

*Recommended article*

**Return value if in range using multiple tables**

How to use multiple lookup tables and return corresponding value if in range

**Explaining **VLOOKUP formula **in cell C10**

**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 sorted in ascending order.

If range_lookup is FALSE the table_array does not need to be sorted however, then the VLOOKUP function won't work in our case, if there isn't an exact match.

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

### INDEX + MATCH

**Formula in cell C10:**

The lookup range must be sorted, just like the LOOKUP and VLOOKUP functions. Functions in this formula: INDEX and MATCH

**Thanks JP!**

*Recommended article*

**Two-way lookup in multiple cross reference tables**

Lookups in multiple cross reference tables

#### Explaining INDEX+MATCH 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 rangev3.xlsx

(Excel 2007 and later versions, Workbook *.xlsx)

### 23 Responses to “How to return a value if lookup value is in range”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.

Maybe oversimplifying it but wouldn't this solve your vlookup problem?

https://www.excelvlookuphelp.com/how-do-i-use-a-vlookup-where-the-range_lookup-is-true-rather-than-looking-for-an-exact-value-looking-for-a-range/

I have been searching for such formula...

May i know hot to do if value columns are more than one like Value-I, Value-II,Value-III and inputs are number and value type

Data is Range-I|Range-II|Value-I|Value-II|Value-III

Inputs are Number|Value-I

A formula which matches range of numberical input and match value type and copies the intersecting value...

To be simple two way lookup.

Hi

What if I want to use the same formula, but to change the range of the two columns B and C to be date range (07/01/2006 , 06/30/2007), and to change the value of column D to be a percentage (%450)

How can I use this formula please?

Alternatively this formula can also be applied:-

=LOOKUP(2,1/(($A$1:$A$3=F2)),$C$1:$C$3)

The first '2' represents the number of column to be searched for.

the expression '1/' represents the column number in the array.

'*' represents AND command.

The command will check the condition of whether F2 is greater than or equal to the values mentioned in the column 1. and then checks the condition that F2 is less than the value mentioned in the column 2 in the same row. Then displays the value corresponding to the same row in the column C.

Thank you all

It worked

I mean this: "=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)"

I think it was the best solution

Hi All

I need some help to add rows to a spreadsheet based on the input of the user.

I ask the user to input the number of rows they need to add by inputting the value into a cell k6. Ideally I'd like them to press a nacro button which would the add the specified number of rows below row 8, keeping the same formulas in E8, G8, H8 and I8.

Can anyone assist?

ian

Hi Ian,

I made a workbook for you:

https://www.get-digital-help.com/wp-content/uploads/2010/01/Ian-inset-rows.xlsm

I continue to get #NAME? result using the first formula

My formula is:

=INDEX(F77:F80, SUMPRODUCT(--($D$83=D77:D80), ROW(A1:A4))) + ENTER

$D$83 is the Weight of a product, in numerical format.

Where F77:F80 is a list of prices (based on weight breaks)

D77:D80 is the min weight in a weight range

E77:E80 is the max weight in a weight range

As example of what would be in D77:F80

200,000 | 299,999 | 14.05

300,000 | 349,999 | 13.10

400,000 | 449,999 | 15.60

350,000 | 399,999 | 13.50

IF D83 is 330,327.

The result should be 13.10

Any advice?

I'm trying to create a formula in 4 different cells ”G3 – G6”, to search a column “B3 – B32” (ie: 4/1/17 – 4/30/17) filled w/dates and add a value from column “C3 – C32” if the date falls between the specified date range (ie: cell G3 reads column "B" for dates between 4/1-4/7, cell G4 reads column "B" for dates between 4/8-4/14, cell G5 reads column "B" for dates between 4/15-4/21 & cell G6 reads column "B" for dates between 4/22-4/30).