Jyggalag

Board Regular
Joined
Mar 8, 2021
Messages
51
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Dear all,

I am currently sitting with a rather large sheet of data. I have made a smaller, and more simple data sheet for simplicity's sake.

Currently I have the following information:

1631632886706.png


As you can see, the data for 2016 has already been filled out (through an old and more manual method). However, I would like for my 2017 column to link to my sheet entitled "Test data" and return the necessary values to each cell. The test data sheet looks like this:
1631633169638.png


Please note that I am relatively new at Excel with limited experience in complex formulas. I can only assume that I would need to create some sort of Iferror(IndexMatch) formula in order to solve this puzzle.

Can anybody please assist me in what I need to change my row values to (maybe in order for them to co-align better?) and what kind of Formula I can use to copy paste through my column in the first picture in order to fill out my data for the 2017 column?

Thank you so much everybody! :)

Let me know if you want me to upload my test file or anything else by the way!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Jyggalag

Board Regular
Joined
Mar 8, 2021
Messages
51
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I imagine that maybe I can index the top column in the test data with an index column in the test sheet right? or?
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
240
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Since your headers are not consistent between sheets, neither in name nor order, you may want to just do the column mapping manually and use formulas like those in the example below.

Book1
ABCDEFGHIJKLMN
1Col TestNameEuropeAsiaCol Test ->2345678
24A11.1
36B22.2TestData V
45C33.3
58D44.4RegionEchoGolfAlphaCharlieBravoFoxtrotDelta
62E55.5Europe5713264
77F66.6Asia5.57.71.13.32.26.64.4
83G77.7
9
10
11If you don't want to add column Col Test then you can hard code the column mapping like this
12
13NameEuropeAsia
14A11.1
15B22.2
16C33.3
17D44.4
18E55.5
19F66.6
20G77.7
21
Sheet1
Cell Formulas
RangeFormula
C2:D8C2=INDEX(TestData,COLUMN(B:B),$A2)
B14:C20B14=INDEX(TestData,COLUMN(B:C),{4;6;5;8;2;7;3})
Dynamic array formulas.
Named Ranges
NameRefers ToCells
TestData=Sheet1!$F$5:$M$7B14, C2:D8
 

Jyggalag

Board Regular
Joined
Mar 8, 2021
Messages
51
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

UPDATE: I have a new issue, please read this post everybody:

 

Jyggalag

Board Regular
Joined
Mar 8, 2021
Messages
51
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
UPDATE: I have a new issue, please read this post everybody:


Please note, as of the update, that I have changed the tables from the screenshots, my formula and how I want to refer to my data. You may ignore the top of this post.

Thank you everybody! And apologies Fluff for the potential double error :)
 

Jyggalag

Board Regular
Joined
Mar 8, 2021
Messages
51
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Since your headers are not consistent between sheets, neither in name nor order, you may want to just do the column mapping manually and use formulas like those in the example below.

Book1
ABCDEFGHIJKLMN
1Col TestNameEuropeAsiaCol Test ->2345678
24A11.1
36B22.2TestData V
45C33.3
58D44.4RegionEchoGolfAlphaCharlieBravoFoxtrotDelta
62E55.5Europe5713264
77F66.6Asia5.57.71.13.32.26.64.4
83G77.7
9
10
11If you don't want to add column Col Test then you can hard code the column mapping like this
12
13NameEuropeAsia
14A11.1
15B22.2
16C33.3
17D44.4
18E55.5
19F66.6
20G77.7
21
Sheet1
Cell Formulas
RangeFormula
C2:D8C2=INDEX(TestData,COLUMN(B:B),$A2)
B14:C20B14=INDEX(TestData,COLUMN(B:C),{4;6;5;8;2;7;3})
Dynamic array formulas.
Named Ranges
NameRefers ToCells
TestData=Sheet1!$F$5:$M$7B14, C2:D8
Dear Jgordan,

Thank you very much for your response!

I appreciate your response, and thank you very much for the formulas!

Unfortunately, I want to keep my structure as it is, but I have updated it accordingly so it should fit now. However, I am currently facing the issue that I want to transpose the data that I refer to so I can pull my formulas vertically down. Can you assist in this?

Thank you! :)
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
240
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Copy the original format, go to where you the transposed data to go, select the top left cell, Paste menu > Paste Special > check the transpose box > click OK
 

Forum statistics

Threads
1,144,374
Messages
5,723,988
Members
422,529
Latest member
mbilal429

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top