Please help me create an IndexMatch-like formula

[Jyggalag]

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:

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:

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!

 

[Jyggalag]

I imagine that maybe I can index the top column in the test data with an index column in the test sheet right? or?

 

[Jyggalag]

?

 

[JGordon11]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Col Test	Name	Europe	Asia		Col Test ->	2	3	4	5	6	7	8
2	4	A	1	1.1
3	6	B	2	2.2			TestData V
4	5	C	3	3.3
5	8	D	4	4.4		Region	Echo	Golf	Alpha	Charlie	Bravo	Foxtrot	Delta
6	2	E	5	5.5		Europe	5	7	1	3	2	6	4
7	7	F	6	6.6		Asia	5.5	7.7	1.1	3.3	2.2	6.6	4.4
8	3	G	7	7.7
9
10
11	If you don't want to add column Col Test then you can hard code the column mapping like this
12
13	Name	Europe	Asia
14	A	1	1.1
15	B	2	2.2
16	C	3	3.3
17	D	4	4.4
18	E	5	5.5
19	F	6	6.6
20	G	7	7.7
21
Sheet1

Cell Formulas
Range	Formula
C2:D8	C2	=INDEX(TestData,COLUMN(B:B),$A2)
B14:C20	B14	=INDEX(TestData,COLUMN(B:C),{4;6;5;8;2;7;3})
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
TestData	=Sheet1!$F$5:$M$7	B14, C2:D8

 

[Jyggalag]

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

Please help me wrap up my IndexMatch formula (90% done)

Dear all, I am currently having some issues with a mix of IFERROR, INDEX, MATCH and TRANSPOSE and ROWS formula in Excel: I have the current sheet of data: The formula and the data links to this test sheet of data: Now I have done about 90% of the work (I hope), seen as my...

www.mrexcel.com

 

[Jyggalag]

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

Please help me wrap up my IndexMatch formula (90% done)

Dear all, I am currently having some issues with a mix of IFERROR, INDEX, MATCH and TRANSPOSE and ROWS formula in Excel: I have the current sheet of data: The formula and the data links to this test sheet of data: Now I have done about 90% of the work (I hope), seen as my...

www.mrexcel.com

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Col Test	Name	Europe	Asia		Col Test ->	2	3	4	5	6	7	8
2	4	A	1	1.1
3	6	B	2	2.2			TestData V
4	5	C	3	3.3
5	8	D	4	4.4		Region	Echo	Golf	Alpha	Charlie	Bravo	Foxtrot	Delta
6	2	E	5	5.5		Europe	5	7	1	3	2	6	4
7	7	F	6	6.6		Asia	5.5	7.7	1.1	3.3	2.2	6.6	4.4
8	3	G	7	7.7
9
10
11	If you don't want to add column Col Test then you can hard code the column mapping like this
12
13	Name	Europe	Asia
14	A	1	1.1
15	B	2	2.2
16	C	3	3.3
17	D	4	4.4
18	E	5	5.5
19	F	6	6.6
20	G	7	7.7
21
Sheet1

Cell Formulas
Range	Formula
C2:D8	C2	=INDEX(TestData,COLUMN(B:B),$A2)
B14:C20	B14	=INDEX(TestData,COLUMN(B:C),{4;6;5;8;2;7;3})
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
TestData	=Sheet1!$F$5:$M$7	B14, 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]

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