Suggest formula for matching columns in large data dump from data source

All,

I have a spreadsheet that pulls data from a large database with over 100+ columns. When I pull the data from the system to populate the spreadsheet, the columns are out of order and I can't fix. Basically:

1) Spreadsheet 1 has 100+ columns of data that formulas throughout the workbook will read data from based on the column names.
2) Spreadsheet 2 is a data dump with the same data as Spreadsheet 1 or maybe extra columns that are unneeded.
3) Spreadsheet 2's columns are out of order compared with Spreadsheet 1.

Any suggested paths to look down?

INDEX/MATCH or OFFSET/MATCH combos would probably do it.

Formula:

OFFSET('Spreadsheet 1'!$A:$A,0,MATCH("Heading Name",'Spreadsheet 1'!$1:$1),0)-1)

as a range within a formula will return the column headed with "Heading Name", assuming the column headers are in row 1 of Spreadsheet 1, and that there are no blank columns before the last column of data.

The match part makes sense - scan the column until you match the headings. But why A:A? Does the formula offset based on the first columns. I use excel so much that I should really know this stuff by now

You need to give the OFFSET formula a starting point to offset from. Using the first column makes sense. Using the entire column means that the offset will also return an entire column.

I've attached a basic example with the ref error. Note that columns 1 and 2 are mixed up in the two tabs. I've been trying to fix it but to no avail. Thanks for your help everyone.

That makes sense. Getting a ref error and not sure why. My formula looks like this:
=OFFSET('Air Detail with Reportable Fiel'!$A:$A,0,MATCH(A$1,'Air Detail with Reportable Fiel'!1:1),0)-1

It's looking at the heading name in A$1, and trying to pull the data from the other file. On reflection this looks right but maybe something missing?

Sorry - as you didn't upload a sample file I did the formula just on here, not in Excel so didn't pick up my error. Try this instead:

Formula:

OFFSET('Air Detail with Reportable Fiel'!$A:$A,0,MATCH(A$1,'Air Detail with Reportable Fiel'!1:1,0)-1)

You won't be able to use this as a stand alone formula as the result is a range of cells, in this case an entire column. You would use this as part of another formula to refer to the column you wanted. Eg

Formula:

=sumif(OFFSET('Air Detail with Reportable Fiel'!$A:$A,0,MATCH(A$1,'Air Detail with Reportable Fiel'!1:1,0)-1),">1000")

Thanks for trying. As you mentioned the first solution returns only the first row. The second solution gives me "0" as the returned value, and doesnt' work for the other rows.

If you had uploaded a sample file to start with we could have gotten there a lot quicker, but I have used INDEX/MATCH rather than OFFSET/MATCH in your attached example file, using the Record Key as a unique identifier.

Example Book.xlsx

This is basically the same formula but allows for any number of rows of data:

Formula:

=INDEX('Air Detail with Reportable Fiel'!$A:$I,MATCH(BCDAir!$A2,'Air Detail with Reportable Fiel'!$B:$B,0),MATCH(BCDAir!B$1,'Air Detail with Reportable Fiel'!$1:$1,0))