Using IF and Index together

[Monica at Plastics]

I have a spread sheet that if the number of units is greater than zero I have it returning the name of the product. I want to use index with that. First do i have to use match with it? How can I use the if and index together? Here is a formula i was trying to use.
Column B is the part name and column U is the quantity.
=IF(INDEX($B$4:$B$264,AGGREGATE(15,6,(ROW($B$4:$B$264)-ROW($B$4)+1)/($U$4:$U$264>0),ROWS($B$267:B267))),($B$4:$B$264,"")))

I keep getting an error on this formula

 

[AhoyNC]

Try:

=IFERROR(INDEX($B$4:$B$264,AGGREGATE(15,6,(ROW($B$4:$B$264)-ROW($B$4)+1)/($U$4:$U$264>0),ROWS($B$267:B267))),"")

 

[Monica at Plastics]

That worked for the first row but if I try to drag the formula down I get #Num !

 

[AhoyNC]

It's working for me, see example below.

Excel Workbook
	A	B	C	T	U	V	W
1
2
3		Part #			Quanity
4		Part1			2
5		Part2			3
6		Part3			0
7		Part4			4
8		Part5			0
9		Part6			5
10		Part7			4
11		Part8			0		Part1
12							Part2
13							Part4
14							Part6
15							Part7
Sheet

 

[Monica at Plastics]

I saved the sheet and repasted it and it is working now Thank you for all your help!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Coachmannyz8]

Good afternoon AhoyNC,

can you assist me with this IFERROR array formula; I can't seem to get rid of the 0 (zero) when I copy the formula to the rest of the column.
{=IFERROR(INDEX('RAWDATA'!$N:$N,SMALL(IF('ER Projects'!$B$4='RAWDATA'!,$R:$R,ROW('RAWDATA'!$R:$R)-MIN(ROW('RAWDATA'!$R:$R))+1,""),ROW()-3)," ")}

Once all the data is displayed, I receive zeros for blanks coming from the raw data sheet. I thought that by adding the ," ") at the end, that it would blank out the cell but its not. Thank you

 

[AhoyNC]

The IFERROR is only going to return " " if the formula produces an error like #VALUE , #N/A, etc.

Without knowing how your data is set up this is a guess, but maybe try something like this:

[TABLE="width: 1180"]
<colgroup><col width="1180"></colgroup><tbody>[TR]
   [TD="width: 1180"]=IFERROR(INDEX(RAWDATA!$N:$N,SMALL(IF('ER   Projects'!$B$4=RAWDATA!$R:$R,[COLOR=#ff0000]IF(RAWDATA!$N:$N<>""[/COLOR],ROW(RAWDATA!$R:$R)-MIN(ROW(RAWDATA!$R:$R))+1,"")),ROW()-3)),"")
[/TD]
 [/TR]
</tbody>[/TABLE]

Change the part in red to the column in your data that has the blank cells.

 

[Coachmannyz8]

Thanks, that worked!

I appreciate AhoyNC......take care.

 

[AhoyNC]

You're welcome. Thanks for the feedback.