# Formula to return value from one column when condition is met for the first time in another

Example:

Given a spreadsheet with four columns - A, B, C, and D, and row 1 is a header row...

A2=1
B2=(Random positive integer)
C2=(Another random positive integer)

A3=A2+1
B3=B2-C2
A4=A3+1
B4=B3-C2

...

D2 is the problem cell. In D2, I want to return the value for column A that matches the first instance of a value in column B that is <=0.

-

In a similar vein to Paul's answer, I would use `Index` and `Match`

``````=INDEX(\$A:\$A,MATCH(0,\$B:\$B,-1)+1)
``````
1. Use whole columns so it doesn't need to be adjusted for the amount of data in the column.
2. Use `Index` rather than `Offset` since `Offset` is volatile and `Index` is not. (Volatile functions recalculate on every sheet calc, while non-volatile functions recalculate only when referenced data change. Too many volatile functions can slow down Excel.)
-
I'm not sure why volatile vs. non-volatile is relevant here. Could you explain a bit more? – Iszi May 9 '12 at 19:19
Simply because a lot of volitile functions on a sheet will slow down recalculation. – chris neilsen May 9 '12 at 19:57
That's what I thought, but was wondering if there was some other reason. This seems the most versatile of the options given, and works for me. Thanks! – Iszi May 9 '12 at 20:01
If you want the MATCH to be with the first value <0 then I don't think that will always work, try changing B4 to 0.....this alternative version should work `=INDEX(\$A1:\$A100,MATCH(TRUE,INDEX(\$B1:\$B100<=0,0),0))` - that also doesn't rely on column B being sorted desecending – barry houdini May 9 '12 at 21:13
@barryhoudini Maybe suggest that as a separate answer, and give a bit more explanation? For example, I'm confused by your use of TRUE as an argument for MATCH. Also, cell references should still be entire columns, like in chris' example - the size of my data set will vary. – Iszi May 9 '12 at 21:31

You can use MATCH to get what you need.

`MATCH(0,B2:B6,-1)+1` will look through `B2:B6` looking for the first value that is equal to or lower than the number `0` and return its relative position. It is indexed from zero, so we add one to match your index.

Indexed from zero means that row B2 is 0, B3 is 1, B4 is 2 and B5 is 3. So MATCH will return "3".

You have your own index in column A, and if this was non-sequential, or contained other values, or didn't start from 1, you could use offset to get to it:

``````=OFFSET(A2,MATCH(0,B2:B6,-1),0)
``````

So this is using the result of the MATCH to count down from A2 to find the value at that row position.

-

couldn't you calculate that using just B2 and C2?

`=CEILING(B2/C2,1)+1`

As per my comment in Chris' answer - updated version

`=INDEX(\$A:\$A,MATCH(TRUE,INDEX(\$B:\$B<=0,0),0))`

This should avoid the problem inherent in Chris' suggestion in that you get the wrong value when the first value <=0 is 0 itself.

The part `\$B:\$B<0` returns an "array" of `TRUE/FALSE` values, the first `TRUE` obviously co-inciding with the first column B value <0, MATCH then finds the position of that first instance and INDEX then gets the corresponding value from column A.

The second INDEX is only there to avoid "array entry" - it works without that, i.e.

`=INDEX(\$A:\$A,MATCH(TRUE,\$B:\$B<=0,0))`

.......but that version would need to be "array entered" - i.e. confirmed with `CTRL+SHIFT+ENTER`.

It's a little more inefficient than the previous suggestions, using the whole column (and that doesn't work in Excel 2003 or earlier - in those versions you need to use a specific range).

Note that MATCH with "match type" of -1 as per Chris' suggestion needs to have descending values in column B - this formula works however column B is ordered.

-
Way to work around the given problem. I could use this for some applications, but part of the problem with this lies outside the given example - in the real spreadsheet, the decrement changes after a certain point. So, I really need to get the "find first negative" effect. – Iszi May 9 '12 at 19:33
Also, in the real spreadsheet, the data I want returned is non-numerical. Thank you for drawing my attention to essential details I should keep in mind, when asking future questions. – Iszi May 9 '12 at 20:02
OK, apologies, trying to be too clever :( - I posted a comment in Chris' answer..... – barry houdini May 9 '12 at 21:14