How to find value >0 and return content of adjacent cell...

I need to have a formula that will look at 24 specific cells to find the one whose amount is >0. I can get that part, but what I want to return in the cell is the value in the cell ADJACENT to the one that is >0.

I started with an IF OR formula (currently only showing 5 cell options for brevity) but I'm open to suggestions: =IF(OR(A1>0,A2>0,A3>0,A4>0,A5>0),B1,0)

Where I have "B1" is where I need to fill in something that will make it read the adjacent of the pertinent cell, ie - if A3 is the one with the value greater than 0, then the value of B3 is what will be returned.

Have I explained this right? Anyone have any ideas?

=SUMPRODUCT((A1:A24>0)*(B1:B24)) worked great and allowed me to sub in any column or row into either portion of the formula, so long as both were either row or column (not one of each). I appreciate the advice!

Originally Posted by Special-K

If column B are all numbers this should work

=SUMPRODUCT((A1:A24>0)*(B1:B24))

Update: This will return ALL values where column A > 0, this may not be what you want

Can someone tell me if they have a formula that work when column B are texts?

Assumptions:

1) the cells are a contiguous range, eg, A1:A24 for the test range, and the return range is adjacent to it, eg B1:B24


2) There is one-and-only-one cell that is more than zero

=INDEX(B1:B24, MATCH(MAX(A1:A24), A1:A24, 0))

Note than since it's looking for the maximum value to start with, if the range is from -25 to -1, then it will return the value corresponding to -1; and if it's {-25, ..., 0, 1, 5} then it will return corresponding to 5 despite there being more than one value >0.

Are the error modes for the data going to present a problem?