Urgent Excel Help - VLookup + Match?

[Jordan155]

Hello Everyone,

I have run into a problem with the document I am trying to create and was hoping that somebody could help. I am not entirely sure how to add pictures and such to the message so I will try and explain my issue as clearly as I can and hopefully someone out there will have an answer for me.

Current I have an Excel document with 11 TABS (11 Sheets) Each of these sheets contain many columns and rows, some columns are unique to that sheet but there are 4 in particular which are the same for all 11 sheets. These columns are NOT always in the same column number (i.e "Name" is in column A on sheet 1, in Column B in sheet 2.. etc)

What I am trying to do is create a MASTER REPORT compiling the info I need from all 11 sheets. Here I have inputted the Master List of "Names" into Column A. What I am trying to do is tell excel to look in each of the 11 other sheets, FIND this Column - IF in this column these names match up - copy and give me the "Last Name" Column value for example:

MASTER SHEET: SHEET 1:

Name Address Name Last Name
1 1 1 A
2 2 2 B
3 3 3 C
4 4 4 D
5 5 5 E

So I want excel to FIND: Column entitled "Name" in Sheet 2 (Not always in the same column) See if any of the entries match (1,2,3,4,5...) and then output on my MASTER SHEET another value (Last Name) in this case.

I have been googling and I have found that the VLOOKUP function could do this if the NAME and LAST NAME are always in the same column BUT THEY ARE NOT - how can I find the COLUMN NAME and do this?

Thanks in advance,
Jordan155

 

[Jordan155]

Please note the formatting of the example did not show correctly - each header should have one of those values, respectively.