Evaluating next lower cell if top cell doesn't meet criteria in an IFS statement

Description in title is very vague as I didn't quite know how to describe what I am looking to do. But here goes:

I have a worksheet titled"Required Tools". If a particular trade (HVAC, Electrical, etc), is selected a list of required tools (with cost, manuf, vendor, etc) is displayed. I have a column titled "Order?" on this sheet. If the individual wants to add this tool to the purchase order form they place an x in the box next to the item.

I have another worksheet titled "Plumb Order Test". Here is what I want to do:

In the first available cell under "Item" I want a formula that will look at the "Required Tools" worksheet and return the item description of the first row that has an "X" in the "Order?" cell. The next cell down would then do the same evaluation, entering the next row in "Required Tools" that has an "x" in the "Order?" cell, making sure not to duplicate any results.

Any help would be appreciated:

tool1.jpgtool2.jpg

You have a worksheets but we have a pictures only

To attach an Excel file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

attachment added

Where is a data?

ok, found it

Update your profile about Office/Excel version: You've Office 365 subscription

Updated. Can you provide any assistance with this?

In C6 of "Plumb Order Test"

=IFERROR(INDEX('REQUIRED TOOLS'!$C$5:$C$1000,SMALL(IF('REQUIRED TOOLS'!$F$5:$F$1000="x",ROW('REQUIRED TOOLS'!$C$5:$C$1000)-ROW($C$5)+1,""),ROWS($C$5:C5))),"")

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

Copy down

see the attached

Everything seems to work fine at home. I uploaded to my onedrive account and when I open at work I get the #NAME? error. What would be causing this?

Probably at work you've Excel less than 365 for subscribers which doesn't support IFS() function

Thank you. Works now.

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)

If you did it already - ignore it.
Thank you.