Excel Lookup Data Based on Column Name not Cell Name

Question

I am trying to accomplish a strange task in excel and don't know how to go about it. I'm using Excel 2007 at work and I know very basic vba.

I want to automate a process where a person takes three spreadsheets and dumps certain data from them into one master sheet. The three spreadsheets vary every month in their column order, so unfortunately I can't just program vlookups to run and get the data.

I could be wrong, but it seems like Vlookup Match, Index Match Match, etc. wouldn't work either because they are still referencing cells. I basically need something that will find a column based on the text in the column, rather than its location, because the location will change, but the text will always be the same.

I have two ideas but I have no idea if they work and don't really know where to start on implementing them:

1. Convert the three spreadsheets to tables and reference the headings with table syntax (I haven't been able to get this to work)
2. Complex VBA that IDs everything

Can anyone help point me in the right direction to accomplish this task? Thanks so much for your help.

EDIT EXAMPLE

My main template that I'm trying to dump things into is just going to have the ID#s and empty columns:

ID#          AltID#          Deal
1
2
3
4
5

And then another spread sheet might have look like this

ID#          AltID#          Deal
1            10101           AAAA
2            20202           BBBB
3            30303           CCCC
4            40404           DDDD
5            50505           EEEE

I could of course vlookup, but the problem is next month, those columns in the second spreadsheet won't be in the same place, in the same order. They could be all the way on colum DD or whatever. So I need a formula that looks them up regardless of the columns location. Just matching them by the heading text. I hope that clarifies...

Answer 1

If you combine VLOOKUP with MATCH you can search based on column name, not index. Remember that the MATCH function returns the numeric index of the match. Let's assume that your data looks like the below:

NAME    DAY1    DAY2    DAY3
Bob     123     345     567
Tim     456     789     998

A functioning Vlookup to return the DAY2 column for Tim would look like:

=VLOOKUP("Tim", A2:D3,MATCH("DAY2",A1:D1,0),FALSE)

A couple of notes. Where I've hard coded in words, you can use cell references.