IFERROR INDEX ROW Formula Not calculating

[bradyman97]

I need some help with this formula. I need this formula to look in column 5 of the PR sheet and look for the words Partial, Open and Needs Ordered and return the number in column A that the words Partial, Open and Needs Ordered is in.

T5 has a 5 in it
T4 is blank

{=IFERROR(INDEX(PR!A$2:R$400,SMALL(IF(OR(INDEX(PR!$A$2:$R$400,,5)="Partial",INDEX(PR!$A$2:$R$400,,5)="Open",INDEX(PR!$A$2:$R$400,,5)="Needs Ordered"),ROW(INDEX(PR!$A$2:$R$400,,5))-ROW(PR!$E$1),""),$T$5-ROW($T$4)),1),"")}



This formula seems to work for just the word Partial

{=IFERROR(INDEX(PR!A$2:R$400,SMALL(IF(INDEX(PR!$A$2:$R$400,,5)="Partial",ROW(INDEX(PR!$A$2:$R$400,,5))-ROW(Balance!$E$1),""),$T$5-ROW($T$4)),1),"")}

 

[bradyman97]

What I need is a formula that reads all the open, partial , and needs ordered in column 5 and returns the material # in cells by themselves

Thank you in advance

Material #	Description	Qty on hand	Re-order point	Needs ordered
107400	washer fluid	0	4	open
109947	plastic fork	225	163
200112	wood handle	6	15	partial
205630	undershirt	160	260	partial
312771	jeans	457	328
319985	large trash bags	75	150	needs ordered

<tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	Material #	Description	Qty on hand	Re-order point	Needs ordered		Material
2​	107400​	washer fluid	0​	4​	open		107400​
3​	109947​	plastic fork	225​	163​			200112​
4​	200112​	wood handle	6​	15​	partial		205630​
5​	205630​	undershirt	160​	260​	partial		319985​
6​	312771​	jeans	457​	328​
7​	319985​	large trash bags	75​	150​	needs ordered

<tbody>
</tbody>

g2=IFERROR(INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7<>"",ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$2:A2))),"") control +shift+enter copy down

 

[bradyman97]

My sheets has 289 material #'s so I changed the 7's to 289 and I put it in S2 and I'm getting blank cells. What did I do wrong?

 

[MARZIOTULLIO]

Hi


Try

s2=IFERROR(INDEX($A$2:$A$289,SMALL(IF($E$2:$E$289<>"",ROW($A$2:$A$289)-ROW($A$2)+1),ROWS($A$2:A2))),"")

control+shift +enter copy down

 

[bradyman97]

Hi
That didn't work either it gives me blank cells too