Create dynamic list by looking up matrix

Hoping to get some advice on the below problem.

Trying to create a dynamic list from a matrix.

I have created a matrix in one worksheet that cross references the tool/equipment with the Job Type. Tools are listed in column A and then the job type in row A5:K5. If the tool is required for a job type, the corresponding cell will have the number "1". If the cell is blank, the tool is not required (worksheet = 'Stock and Equipment Matrix').

On a separate worksheet (known as 'Job Sheet 1'), I want excel to auto populate the list of tools required dependent on the Job Type identified in Cell A13. The list should be populated from cell A17 downwards and not leave any blank rows.

I've been playing around with a variety of formula and can't quite get it to work the way I want.

(also worth noting that the 'Job Sheet 1' cell A13 will auto populate based on other worksheets that are also linked to the list of job types).

Look forward to some feedback

In A17

=IFERROR(INDEX('Stock & Equipment Matrix'!$A$6:$A$31,SMALL(IF(INDEX('Stock & Equipment Matrix'!$B$6:$K$31,0,MATCH('Job Sheet 1'!$A$13,'Stock & Equipment Matrix'!$B$5:$K$5,0))=1,ROW('Stock & Equipment Matrix'!$A$6:$A$31)-ROW($A$6)+1,""),ROWS($A$6:A6))),"")

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

I added a named range "Equipment" (see Column L in 'Stock and Equipment Matrix') to be used in Data Validation in "Pricing Guide".

Originally Posted by JohnTopley

In A17

=IFERROR(INDEX('Stock & Equipment Matrix'!$A$6:$A$31,SMALL(IF(INDEX('Stock & Equipment Matrix'!$B$6:$K$31,0,MATCH('Job Sheet 1'!$A$13,'Stock & Equipment Matrix'!$B$5:$K$5,0))=1,ROW('Stock & Equipment Matrix'!$A$6:$A$31)-ROW($A$6)+1,""),ROWS($A$6:A6))),"")

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

I added a named range "Equipment" (see Column L in 'Stock and Equipment Matrix') to be used in Data Validation in "Pricing Guide".

Thank you for this assistance - greatly appreciated.

However there appears to be an error in the formula (the same one I had trouble with).

Working with your example, I have cross referenced the results the formula generates. There is one weird issue with only one tool - the first on the list "PG450".

This tool does not show for the below job types even though it has a 1 in the stock and equipment matrix
- Grind & Seal
- Polished Concrete
- Glue Removal
- RHINO Glue Removal
- Repair & Re-Seal

AND then - it shows for the Job type 'Spray Paving' when it's not meant to?

I'm not sure if the formula is off or not?

Thanks so much for your help thus far

It does not show because it is not entered with CSE (error in my posting).

It does not show because not CSE entered

Perfect!! thanks so much - you are brilliant

thank you!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.