How do I return a columns values into a different sheet using a column name?

[InfiniteDoors]

Okay, so what I'm trying to do is something similar to a v-look up, except I want to search by a column name.

For instance:

I have Excel file 1 with only two column names, "Fruits" in A1 and, "Vegetables" in D1. So it looks like:

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Fruits
[/TD]
[/TR]
</tbody>[/TABLE]

Vegetables

In Excel file 2 I have again two column names (however there could be other names depending on the workbook) of, "Vegetables" in A1 and "Fruits" in F1. In this file I have items listed under vegetables and fruits. So it looks like:

[TABLE="width: 77"]
<tbody>[TR]
[TD]Vegetables[/TD]
[/TR]
[TR]
[TD]asparagus[/TD]
[/TR]
[TR]
[TD]green beans[/TD]
[/TR]
[TR]
[TD]spinach[/TD]
[/TR]
[TR]
[TD]broccoli[/TD]
[/TR]
[TR]
[TD]cabbage[/TD]
[/TR]
[TR]
[TD]carrots

[TABLE="width: 122"]
<tbody>[TR]
[TD]Fruits[/TD]
[/TR]
[TR]
[TD]orange,banana[/TD]
[/TR]
[TR]
[TD]pear,apple[/TD]
[/TR]
[TR]
[TD]strawberry[/TD]
[/TR]
[TR]
[TD]peach,blueberries[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Now, what I would like to be able to do is something like search the column name, "Fruits" and have it return the columns values that go under the header in File 2 to show in File 1. So essentially it is matching the column header names, then returning the column on where it located the name. Is this possible?

I appreciate the help.

 

[Andrew Poulsom]

Here's an example with all the data on one worksheet (formula in I2 copied down and across). Adjust the references to suit:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Vegetables					Fruits			Fruits	Vegetables
2	asparagus					orange,banana			orange,banana	asparagus
3	green beans					pear,apple			pear,apple	green beans
4	spinach					strawberry			strawberry	spinach
5	broccoli					peach,blueberries			peach,blueberries	broccoli
6	cabbage									cabbage
7	carrots									carrots
Sheet1

Cell Formulas
Range	Formula
I2	=INDEX(INDEX($A$2:$G$10,0,MATCH(I$1,$A$1:$G$1,FALSE)),ROWS(I$2:I2))