# Lookup, match, copy and fill

#### Romefucan

##### Board Regular
Hello Again All:
I would like to say I won't bother anyone on the board anymore, but that would be a lie, I really appreciate the support here.

I have 1 Excel workbook with 4 sheets.
Sheets 1 thru 3 contain raw data sorted into columns with column headings in row 1.
Sheet 4 is the Master Sheet where I want to bring all of the data too from sheets 1 thru 3 however I want the data in the order that I specify by column headings (Row 1).

Basically I want to organize the columns in sheet 4 in a standard format that I specify for further analysis. (column headings in sheets 1 thru 3 are in different orders and can be in different sheets)

What I need is a function that can read my column headings in sheet 4, search sheets 1 thru 3 and find the matching column heading in row 1 and copy the entire 1000 rows of data from each column into the correct matching column headings in sheet 4.

Sounds easy but I am new to lookup functions especially across multiple sheets.

Kind Regards
Romefucan

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi,

Here is one idea:

B2: =MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B1,Sheet1!\$A\$1:\$H\$1,0),MATCH(B1,Sheet2!\$A\$1:\$H\$1,0),MATCH(B1,Sheet3!\$A\$1:\$H\$1,0)))
Drag right

B3: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B1,Sheet1!\$A\$1:\$H\$1,0),MATCH(B1,Sheet2!\$A\$1:\$H\$1,0),MATCH(B1,Sheet3!\$A\$1:\$H\$1,0)))
Drag right

B4: =INDEX(CHOOSE(B\$2,Sheet1!\$A2:\$H2,Sheet2!\$A2:\$H2,Sheet3!\$A2:\$H2),B\$3)
drag right and down as far as needed.
Book1
ABCD
2Sheet213
3Column471
4Data111331
5222442
6333553
7444664
8555775
9666886
Sheet4

Hi

Fairwinds
Wow, I really appreciate the reply. Maybe the problem wouldn't look so scary if I started with just a 1 column lookup and expanded from there.

I've been reading up on Match and Lookup Functions and assumed I could use the 2 combined somehow, but I totally missed the Index Function. I need to read up on it.

I need to re-arrange a few things in my project but I will try your idea and reply back.
Thanks again

Kind Regards
Romefucan

Code Results

Fairwinds
I made a few minor modifications to your code (basically just moved cells down 1 row) to fit my project.

When I paste the code into 1 cell Excel returns an error stating "File Not Found" and it continues to ask me to link the cell to a file and sheet. When I cancell it returns the #NA error in the cell. There is no other file to link anything to as I am only using the 4 sheets in the 1 file called data scrubber.xls.

Is the code trying to reference your source file still? Modified Code is below:

B2: =MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,Raw Data 1!\$A\$2:\$H\$2,0),MATCH(B2,Raw Data 2!\$A\$2:\$H\$2,0),MATCH(B2,Raw Data 3!\$A\$2:\$H\$2,0)))
Drag right

B3: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,Raw Data 1!\$A\$2:\$H\$2,0),MATCH(B2,Raw Data 2!\$A\$2:\$H\$2,0),MATCH(B2,Raw Data 3!\$A\$2:\$H\$2,0)))
Drag right

B4: =INDEX(CHOOSE(B\$3,Raw Data 1!\$A3:\$H3,Raw Data 2!\$A3:\$H3,Raw Data 3!\$A3:\$H3),B\$4)
drag right and down as far as needed.

Kind Regards
Romefucan

As you have spaces in the sheet name, there needs to be single quotes before and after the sheet names such as:

=MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,'Raw Data 1'!\$A\$2:\$H\$2,0),MATCH(B2,'Raw Data 2'!\$A\$2:\$H\$2,0),MATCH(B2,'Raw Data 3'!\$A\$2:\$H\$2,0)))

Code Victory

Fairwinds
You are exactly correct, not enough quotes. You scored another victory.
The code works perfectly. You are a Guru amongst Gurus.

Thank you for your time and expertise.

Fairwinds to you always

Kind Regards
Romefucan

Replies
0
Views
125
Replies
1
Views
206
Replies
3
Views
254
Replies
2
Views
232
Replies
2
Views
368

1,217,764
Messages
6,138,474
Members
450,141
Latest member
Hal5000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back