Excel - Find a specific column in a CSV file, and copy to the current sheet

Question

I get a regular data export in a CSV format, which I like to pull into excel and report on via helper columns and pivot tables.

I do this by linking to the csv file, and copying the cell contents from the columns of data I'm interested in.

I use something like =if('file path[filename.csv]Sheet1'!A1<>"",'file path[filename.csv]Sheet1'!A1,"")

I then auto fill down the entire column.

This works well, and pulls the data into my current sheet, where I can then add a helper column, and run a pivot table from it. Once a week, I overwrite the CSV file with a fresh one, and refresh the pivot, to produce the reports I require.

The problem I have is that occasionally, the business department that produce the CSV that is my source, will add a new field to the data, and as a consequence, all my cell references then get broken, and I have to go and change all my formulas to accomodate the new field, even though I've no requirement for it at all.

All of the columns in the CSV have unique column headers.

What I would like to do is use a formula to find the specific column that I require in the CSV, and then pull it into my worksheet.

Catalogue Number    Price   Purchase Date   Sales Office
1    £500.00    11-Sep  EMEA
2    £606.00    8-Aug   APAC
3    £454.00    3-Jul   NA
4    £2,132.00  29-Jan  NA
5    £548.00    30-Nov  APAC
6    £514.00    23-Apr  NA
7    £36.00     3-Feb   EMEA
8    £998.00    11-Oct  EMEA
9    £1,454.00  11-Sep  EMEA

In the above example (dummy data), for instance, I'd like to identify the column headed 'Price' (D1:D10), and then pull the entire column into a new sheet.

I've tried experimenting with HLOOKUP, INDEX and MATCH, but can't quite work out how to get the entire column. In the example below, you can see what I've been trying, and whilst I can identify the right column, I cant get the autofill to successfully iterate the formula in the way I would require.

Formula "=MATCH("Catalogue Number",5:5,0)"
Output  3

Formula "=MATCH("Price",5:5,0)"
Output  4

Formula "=HLOOKUP("Price",C1:F10,2)"
Output  500

Formula "=HLOOKUP("Price",$1:$1048576,2,0)"
Output  500
    500

Formula "=INDEX($1:$1048576,2,(MATCH("Price",$1:$1,0)))"
Output  500
    500
    500
    500
    500
    500

I'm using Excel 2010.

Answer 1

=INDIRECT("R"&ROW()&"C"&MATCH("Price",$A$1:$F$1,0),0)

This works by creating an INDIRECT() R1C1 cell reference instead of the normal A1 cell reference.

The row is identified using ROW(). The column is populated by using MATCH() to find the column number of the heading in question (counting from the left, A=1).

Note - Make sure your MATCH search criteria goes all the way across to column A (ie $A$1:$F$1, even if your column headers start in column B - otherwise the R1C1 reference will be slightly misaligned.)