Only return cells with a value

[Sky188]

Good afternoon all,

I'm trying to put together a formula that will look through a column for values greater than 0 and return another cell's value for those cells that do have a value greater than 0. For example, if I5 has a value of 6, I6 has a value of 0, and I7 has a value of 2, I'm hoping for a formula that can be put in A1 and A2 that will return the value in D5 (which is linked to the value in I5 being greater than 0), and the value in D7 (which is linked to I7).


Thanks in advance

[JBeaucaire]

Assuming the range of rows to watch is I5:I100, put this array formula into A1:

=INDEX($D$5:$D$100, SMALL(IF($I$5:$I$100>0, ROW($I$5:$I$100)-4, ""), ROW($A1)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Now copy A1 down as far as you want for as many answers as you want to retrieve.

[Tony Valko]

Here's another one if you're using Excel 2010 or later.

Entered in A1:

=IFERROR(INDEX(D:D,AGGREGATE(15,6,1/(I$5:I$10>0)*ROW(I$5:I$10),ROWS(A$1:A1))),"")

Copy down until you get blanks.

Adjust the range I5:I10 to suit.