Using IF and Index together

Monica at Plastics

New Member
Joined
Apr 26, 2019
Messages
12
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try:
Code:
=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))),"")
 
Upvote 0
It's working for me, see example below.
Excel Workbook
ABCTUVW
1
2
3Part #Quanity
4Part12
5Part23
6Part30
7Part44
8Part50
9Part65
10Part74
11Part80Part1
12Part2
13Part4
14Part6
15Part7
Sheet
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
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:

Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top