Returning list of column headers if a value in column A is detected

[Nitrousoxide]

I have a list of chemicals and ingredients in a chart and want to pull in a list of ingredients needed if I enter a the name of a chemical in column A into a cell.

	Chemical B	Chemical A	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4
Chemical A				1	1	1
Chemical B		1		1
Chemical C	1		1

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Here's the table

And then here's the format of the user would enter the chemical in and in the third column how I'd like it to output the value.

Chemical Wanted	Amount Wanted	Chemicals Needed
Chemical A		Ingredient 2
		Ingredient 3
		Ingredient 4

<colgroup><col width="131"><col width="120"><col width="171"></colgroup><tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Is there a way to get it to pull the list of ingredients if someone enters a chemical desired into first cell under "chemical wanted" based on the first table?

 

[Eric W]

Try:

Excel 2012

	A	B	C	D	E	F	G
1		Chemical B	Chemical A	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4
2	Chemical A				1	1	1
3	Chemical B		1		1
4	Chemical C	1		1
5
6
7
8	Chemical Wanted	Amount Needed	Chemicals Needed
9	Chemical A		Ingredient 2	Ingredient 3	Ingredient 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula C9 {=IFERROR(INDEX($D$1:$G$1,SMALL(IF(INDEX($D$2:$G$4,MATCH($A9,$A$2:$A$4),0)=1,COLUMN($D$1:$G$1)-COLUMN($D$1)+1),COLUMNS($C9:C9))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the array formula in C9, confirm with Control+Shift+Enter, and drag right as needed. Change the ranges to match your sheet.