ExcelBanter - Formula Help: When a Number is Reached

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Formula Help: When a Number is Reached (https://www.excelbanter.com/new-users-excel/266322-formula-help-when-number-reached.html)

Formula Help: When a Number is Reached

I need a formula that will reference column A when column B has reached/eclipsed a given number.

Example: When column B range reaches or surpasses "30.00", tell me the corresponding value in column A

The expected, and correct, result of the formula should be "4"

Data
Column A - Column B
1 - 25.05
2 - 27.64481038
3 - 29.99606543
4 - 32.1630163
5 - 34.18397103
6 - 36.08544701
7 - 37.88672718
8 - 39.60236759
9 - 41.2436836
10 - 42.8196825

I need a formula that will reference column A when column B has reached/eclipsed a given number.

Example: When column B range reaches or surpasses "30.00", tell me the corresponding value in column A

The expected, and correct, result of the formula should be "4"

Thanks

Quote:

Originally Posted by morri3sa (Post 960077)

Hello,

I suppose that it's possible to you to use column C or some
other column and write formulas there.

COLUMN A

contain numbers or anything else

COLUMN B

contains those values that you want to check out

COLUMN C

at C1 is following formula

=IF(B1=$D$1;A1;"")

Copy this formula down so far as you have numbers
at column B

COLUMN D

cell D1 contains the search value. At your exsample
number 30

COLUMN E

at cell E1 contains following formula

=MIN(C:C)

according your exsample, this should show number 4
as a result of formula.

Now you have the situation you described and at column C
you can see values only if the corresponding number at
column B is bigger than value in cell $D$1.

Cell E1 shows the smallest number at column C, which is
at the same time, the first one which is shown if you
scroll down the worksheet.

So, give the number to the cell D1 and you will see
which number at column A corresponds the value you
put to D1. This result comes to cell E1

***

If it was me I would just run a macro to loop through column "B" as you listed. In the example I submitted it kicks up a message box telling you what the corresponding number value is when it goes above 35.

Code:

Sub FindValue()

Dim rcount As Integer

Dim a As Integer

'gets a count of how many rows there are in column B

rcount = Range("B" & Rows.Count).End(xlUp).Row

'loops through looking for a value greater than 35 in column B

For a = 1 To rcount

    If Range("B" & a) 35 Then

        MsgBox (Range("A" & a))

        Exit Sub

    Else

    End If

Next a

    

End Sub

Let me know if you need help on how to insert or run a macro.