Value between two numbers returns a cell value in that row

[DJ...]

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.

 

[NeonRedSharpie]

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)?

 

[steve the fish]

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

 

[DJ...]

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?

 

[NeonRedSharpie]

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?

 

[DJ...]

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.

 

[DJ...]

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.333333
805
650
546.6666667
472.8571429
417.5
374.4444444
340
311.8181818
288.3333333
268.4615385
251.4285714
236.6666667
223.75
212.3529412
202.2222222
193.1578947
185
177.6190476
170.9090909
164.7826087
159.1666667
154
149.2307692
144.8148148
140.7142857
136.8965517
133.3333333
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.

 

[NeonRedSharpie]

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.

 

[DJ...]

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.