Index and match array formula

Hi guys,

I am trying to list in Sheet2/ column B, all products made on a given equipment (from “Trains” tab). The corresponding cell where each product is made marked “Y” or “‡”
I am having these issues
1. The formula doesn’t work properly. For example, I selected an equipment but the list is showing different products
2. The list has duplicate product names. Can I remove the duplicate names and sort the list by the highest risk score number)
3. I need to list in column G all product made on the same equipment excluding the worst-case product.

i think it is better to use helper column to calculate column# just once

Tim, A3 is a drop drown menu and column B is dependent on A3. The formula in column B is not working,

Thanks John,

Is there anyway to get rid of the duplicate names in column B. Also, I need to exclude the worst-case product from Column G

list 2.jpg

i inserted a column, and formulas work properly in it

tim list.jpgThank Tim but when I put another equipment in A3, there should be 4 products listed. With your formula I am getting much more. Please see the image.

Sorry I attached John's solution. Here is the file you modified.

Errors in formulae

In B3

=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Trains!$B$13:$B$65,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$3)+1,""),ROWS($C$3:$C3))),"")

in G3


=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Trains!$B$13:$B$65,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$3)+1,""),ROWS($D$6:$D6))),"")


Both ....

...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.

look carefully

Tim, can you please look at the reference table. there are 5 products made on Tonson Mixer A.

I am not sure if I am missing something!

tim.jpg

[QUOTE=JohnTopley;4812268]Errors in formulae

In B3

=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Trains!$B$13:$B$65,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$3)+1,""),ROWS($C$3:$C3))),"")

in G3


=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Trains!$B$13:$B$65,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($E$3)+1,""),ROWS($D$6:$D6))),"")

Thanks John. Can the worst case product (E3) be excluded from the list in column G?

Thank you again

your formula:
=IFERROR(INDEX(Trains!$E$4:$FQ$4,SMALL(IF(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Equipment_List,0),0)="Y",COLUMN(Trains!$E$4:$FQ$4)-COLUMN($C$3)+1,""),ROWS($C$3:$C3))),"")
i did not check up your logic
should be (my version):
=IFERROR(AGGREGATE(15,6,(COLUMN(Trains!$E$4:$FQ$4)-COLUMN($D$3)+1)/(INDEX(Trains!$E$13:$FQ$65,MATCH($A$3,Trains!$B$13:$B$65,),)="Y"),ROWS($D$3:$D3)),"")