If Statement only return data if True

[Mallen89]

I am trying to use an if statement to return a cell value only when the logical test is True. If it is False, I don't want a blank field, I want it to go to the next cell to check if it is True. Make sense?

I have found lots of threads touching base on this topic, but have failed to find a SMALL, INDEX, or IFERROR nesting function that works well for my specific set of data.

Here is what I have... Basically the data is a pick list, and I want to pull the values from row A that have a quantity >0 in row B.

A:A
Apple
Orange
Banana
Kiwi

B:B
0
2
5
0

Now I want to pull the data from row A if the corresponding data in row B is greater than 0. So for this example, I want to return:

C:C
Orange
Banana

I want to use a formula that will return this with no blank rows and not using a filter. Can anyone out there help??

Let me know if you need more info. Thanks

 

[FDibbins]

Perhaps this?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Amt[/td][td][/td][td]Name[/td][td]Amt[/td][/tr]

[tr][td]

2​

[/td][td]Apple[/td][td]0[/td][td][/td][td]Orange[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]Orange[/td][td]2[/td][td][/td][td]Banana[/td][td]

5​

[/td][/tr]

[tr][td]

4​

[/td][td]Banana[/td][td]5[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Kiwi[/td][td]0[/td][td][/td][td][/td][td][/td][/tr]
[/table]
D2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$5>0,ROW($B$2:$B$5)),ROWS($A$1:A1))),"")
this is an ARRAY formula and is entered using CTRL SHIFT ENTER, not just enter.
Then copy down and across as needed

 

[Mallen89]

Perhaps this?

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD]Amt[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Apple[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Orange[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Banana[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Kiwi[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

D2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$5>0,ROW($B$2:$B$5)),ROWS($A$1:A1))),"")
this is an ARRAY formula and is entered using CTRL SHIFT ENTER, not just enter.
Then copy down and across as needed

Tried and it returns a blank cell.

 

[Scott T]

Is this what you want?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]amt
[/TD]
[TD]DV list
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Orange
[/TD]
[TD]2
[/TD]
[TD]Banana
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Banana
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Kiwi
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In C2 and copy down. This is an array formula and must be committed with CONTROL+SHIFT+ENTER.

=IF(ROWS(C$2:C2)<=COUNTIF($B$2:$B$5,">0"),INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5>0,ROW($A$2:$A$5)-ROW($A$1)),ROWS(C$2:C2))),"")

Bring up the Name Manager with CTL+F3 and create a new name with in the refers to

=OFFSET($C$2,0,0,COUNTIF(B2:B5,">0"))

In Data Validation select Allow: List and source:=dvlist Where dvlist is the range name you created above

this should give you a drop down list with no blanks and will change if the amount