Share via

Using formula to get column data from one worksheet to another when the header matches

Anonymous
2021-04-18T18:18:53+00:00

I have two worksheets - Sheet 1 where my data is and Sheet 2 where I extract certain columns that are required from Sheet 1, as the data file is huge and not everything is needed.  The complication I have is that the columns on Sheet 1 can move, depending on who is providing the data.

Sheet 1

Reference Customer OrderDate ProductCodeNo ProductCodeDesc ColourPcode Height Width

Sheet 2

Customer OrderDate Style Width Height Colour

There are more columns that this, but gives a little example.

In Sheet 1, Column D (ProductCodeNo) needs to be shown on Sheet 2 in Column C (Style).  Sheet 2 columns are always the same, but they can move in Sheet 1 depending on who has provided the initial data.

I think I need to use INDEX and MATCH, but am getting a little confused.  Can anyone help?

Many thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-04-18T20:50:20+00:00

    Yes, it does. thanks!

    I'd use a translation table, so that you can easily change column headers if required. On Sheet 2:

    Then, in A2 on Sheet 2, enter the formula

    =INDEX('Sheet 1'!2:2,MATCH(VLOOKUP(A$1,$H$2:$I$7,2,FALSE),'Sheet 1'!$1:$1,0))

    Change Sheet 1 to the actual name of the raw data sheet. As you can see, the VLOOKUP function in the formula refers to the translation table.

    Fill or copy the formula from A2 to the right to F2, then down (or vice versa).

    Here is Sheet 1 with some dummy data:

    And Sheet 2 with the formula results:

    I hope that'll do what you want.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-04-20T15:57:14+00:00

    Thank yo so much for this, it has worked perfectly.

    0 comments
  2. Anonymous
    2021-04-18T20:16:38+00:00

    I don't think I have made myself very clear.  Its difficult trying to explain what I am thinking.

    Sheet1 is called RawData - I get the information from various people which I copy straight into this worksheet.  Problem is the columns change - so the data in Column A can be suddenly in Column J, which is quite annoying as it throws out all my other calculations on Sheet2, but regardless of where the columns are, their respective column headers remain the same.

    Example - ProductCodeNo can be in Column A, then the next time Column J, then Column D, etc.  However the header 'ProductCodeNo' remains the same.

    Sheet2 is called Orders - as I get too much information that I require in the RawData, I only want to see certain columns in Sheet2.

    One of my calculations on Sheet2 is in cell B2 which is looking for the Customer in column E in Sheet1 =IF(RawData!E2="","",RawData!E2), but if the data has moved to a different column like column J, it just shows #N/A.

    I want to be able to link my columns in Sheet2 with their respective columns in Sheet1, so if they have moved, around it would not matter as I can still see the data without messing with my formula every time.  I do have more meaningful header names in Sheet 2, but the data in the rows below will still apply.  

    Does this make it clearer?

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-04-18T19:55:27+00:00

    Do you mean that you want to enter a product code number in C2 on Sheet 2 and formulas should automatically fill n A2, B2, D2 etc.?

    If so, enter the following formula in A2:

    =IFERROR(INDEX('Sheet 1'!$A$2:$H$1000,MATCH($C2,INDEX('Sheet 1'!$A$2:$H$1000,0,MATCH("ProductCodeNo",'Sheet 1'!$A$1:$H$1,0)),0),MATCH(A$1,'Sheet 1'!$1:$1,0)),"")

    Change Sheet 1 to the actual name of that sheet, then fill down.

    Or should all cells in row 2 on Sheet 2 be filled automatically from row 2 on Sheet 1?

    0 comments