# Value between two numbers returns a cell value in that row

#### DJ...

##### New Member
I have a spreadsheet with total usage from my customers of data consumed in a month. Let's assume the cell value here is 429.68. In a separate sheet, I have values between 1 and 5000 in column A, and profitability margin in column B. I'm trying to write a formula that calculates which cell value in the second sheet the value falls within, and returns column B from the appropriate row (profit margin) for that particular usage.

I've tried a median IF statement but this doesn't work.

Any help would be appreciated.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What do the numbers in column A on the other sheet look like? Are they integers (1, 2, 3, 4), floats (1.1, 1.2, 1.3), or ranges (1-2, 3-4, 5-6)?

If its sorted data between 1 and 5000 vlookup with a TRUE (1) should work.

What do the numbers in column A on the other sheet look like? Are they integers (1, 2, 3, 4), floats (1.1, 1.2, 1.3), or ranges (1-2, 3-4, 5-6)?

The numbers in column A are decimal values. I can round them up or down without changing anything, if it makes things easier?

No, it doesn't matter. I'm just trying to figure out what you're trying to match on. So you're looking for the closest value in column A (1-5000 row) that matches 429.68?

If its sorted data between 1 and 5000 vlookup with a TRUE (1) should work.

I unfortunately can't sort by smallest to largest so the result it throws out doesn't work.

No, it doesn't matter. I'm just trying to figure out what you're trying to match on. So you're looking for the closest value in column A (1-5000 row) that matches 429.68?

I'm trying to find the row in sheet B where the value from the cell in sheet A falls between. Let me paste some of the data to help:

Sheet A cell value: 563.81

Sheet B values:

 3130 1580 1063.33 805 650 546.667 472.857 417.5 374.444 340 311.818 288.333 268.462 251.429 236.667 223.75 212.353 202.222 193.158 185 177.619 170.909 164.783 159.167 154 149.231 144.815 140.714 136.897 133.333 130

<tbody>
</tbody>

Those values are in column G at the moment, and I want to return the data for column L of the uppermost row where the value from sheet A falls between. So in the above example, the value I'm looking for falls between rows 10 and 11 in column G of sheet B (there is additional info above this table which is why it's not rows 6 and 7). I want it to return the value in column L for row 10, because 563.81 falls between 546.6666667 and 650.

Last edited:
Code:
``````Function BETWEENVALUES(iCell As Range, searchArea As Range) As Variant

For Each cell In searchArea

If iCell.Value >= cell.Value And iCell.Value <= cell.Offset(1, 0).Value Then
BETWEENVALUES = cell.Offset(0, 1).Value
End If

If iCell.Value <= cell.Value And iCell.Value >= cell.Offset(1, 0).Value Then
BETWEENVALUES = cell.Offset(0, 1).Value
End If

Next cell

End Function``````

Here is a custom function. I don't know if excel has a built in function that will work.

Thanks all. What I did was readjust all of my previous formulas so that I could sort properly by smallest to largest and I'm now able to run the VLOOKUP true function instead, which I decided was the easiest route to take. All is working now - thanks for the assistance.

Replies
3
Views
94
Replies
3
Views
266
Replies
20
Views
474
Replies
3
Views
58
Replies
8
Views
143

1,203,052
Messages
6,053,223
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back