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:

Fruits

<tbody>
</tbody>

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:

Vegetables

asparagus

green beans

spinach

broccoli

cabbage

carrots Fruits orange,banana pear,apple strawberry peach,blueberries <tbody> </tbody>

<tbody>
</tbody>

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