Formula Q: referencing first column number that meets specific criteria

[tkaplan]

I am because it's for sure something really obvious that I am missing.
I have a table in cells D through K with numberic values ranging from 0 to 1Billion. In cell B2 I want to return the reference to the first column where the value is greater than the value in A2.

for example values in row 2 may be:
D: 88,745
E: 108,236
F: 207,469
G: 350,000
etc.

A2 is 125,000

I want B 2 to return 6 for column F because that is the first time where the value of the cell is greater than 125K

I know I can do multiple nested IF statements but this spreadsheet is dynamic and I know there has to be a better way.

Thanks!
T

[Bob Phillips]

Maybe

=MATCH(A2,2:2,1)

[khu]

Are you looking for a worksheet method or would a macro work? It would be very easy to assign a button that looked up and returned the first reference where the value was greater.

Sub Button1_Click()

For Each Number In Range("d2", Range("d2").End(xlToRight))
    If Number.value > Range("a2").value Then Exit For
Next
Range("b2").value = Number.Cells.Address

End Sub

If you insert a button and double click on it, or assign a new macro, you can paste this code into it. This searches each number in the range of data starting at D2 (assuming no blank cells) and then returns the cell address where the value is greater than A2, putting the address in cell B2

[tkaplan]

for some reason my response to xld didn't post yesterday. thanks xld, it worked.
question just for my knowledge: how come if instaed of putting a reference to A2 i put an actual value (ie Match(125000,2:2,1)) i get an error?

khu, i was looking for a formula for a spreadsheet. thank you for taking the time to respond though!

[khu]

khu, i was looking for a formula for a spreadsheet. thank you for taking the time to respond though![/QUOTE]


Yeah, sorry I hadn't realized I kept this open without refreshing before posting. For some reason I was thinking you needed the actual address. I believe that match function posted above will be the cell before the one you're looking for though. The match type of 1 is the largest value that is less than or equal to you're look up value, so unless F2 is equal to A2, it will return the position of E2, since it's the first one less than or equal to A2. You'll have to add one to the match function, =MATCH(A2,2:2, 1)+1 to get the correct position.

Also, not what you asked for, but just to put it out there, for the actual cell address instead of relative position in the search array, you can use =CELL("address",OFFSET(INDEX(D2:K2,1,MATCH(A2,D2:K2,1)),0,1)) to return $F$2.

[Bob Phillips]

question just for my knowledge: how come if instaed of putting a reference to A2 i put an actual value (ie Match(125000,2:2,1)) i get an error?

Works fine for me.

[tkaplan]

Works fine for me.

weird. will have to try again when i'm back in the office. was probably leaving something stupid out.

anyway, issue is solved. so thank you everyone for all of your help