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.

[TABLE="width: 0"]
<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>[TR]
[TD][/TD]
[TD]Chemical B[/TD]
[TD]Chemical A[/TD]
[TD]Ingredient 1[/TD]
[TD]Ingredient 2[/TD]
[TD]Ingredient 3[/TD]
[TD]Ingredient 4[/TD]
[/TR]
[TR]
[TD]Chemical A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Chemical B[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chemical C[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<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.

[TABLE="width: 0"]
<colgroup><col width="131"><col width="120"><col width="171"></colgroup><tbody>[TR]
[TD]Chemical Wanted[/TD]
[TD]Amount Wanted[/TD]
[TD]Chemicals Needed[/TD]
[/TR]
[TR]
[TD]Chemical A[/TD]
[TD][/TD]
[TD]Ingredient 2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ingredient 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ingredient 4
[/TD]
[/TR]
</tbody>[/TABLE]
<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
Chemical B	Chemical A	Ingredient 1	Ingredient 2	Ingredient 3	Ingredient 4
Chemical A
Chemical B
Chemical C
Chemical Wanted	Amount Needed	Chemicals Needed
Chemical A	Ingredient 2	Ingredient 3	Ingredient 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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