IFERROR INDEX ROW Formula Not calculating

New Member
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),"")}

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

New Member
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

 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

Well-known Member
 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

New Member
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

Well-known Member
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

Last edited:

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

Replies
1
Views
130
Replies
8
Views
164
Replies
13
Views
446
Replies
2
Views
168
Replies
4
Views
309

1,190,870
Messages
5,983,325
Members
439,839
Latest member
iblackie

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.

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

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