Lookup Data from Vertical to Horizontal

[kumara_faith]

Hi,

I have the following data table:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
2	ENRS	CONS	INFT	REAL	UTIL	FINL	COND	REAL	COND	ENRS	INFT	HLTH	INFT	INFT	ENRS
3	34.40%	-15.40%	61.70%	32.30%	19.90%	28.80%	43.10%	30.20%	10.10%	27.40%	38.80%	6.50%	50.30%	43.90%	45.60%
4	MATR	HLTH	MATR	COND	CONS	COND	HLTH	UTIL	HLTH	TELS	MATR	UTIL	TELS	COND	FINL
5	22.50%	-22.80%	48.60%	27.70%	14.00%	23.90%	41.50%	29.00%	6.90%	23.50%	23.80%	4.10%	32.70%	33.30%	25.70%
6	UTIL	UTIL	COND	INDU	HLTH	REAL	INDU	HLTH	CONS	FINL	COND	COND	FINL	TELS	REAL
7	19.40%	-29.00%	41.30%	26.70%	12.70%	19.70%	40.70%	25.30%	6.60%	22.80%	23.00%	0.80%	32.10%	23.60%	23.30%
8	INFT	TELS	REAL	MATR	REAL	TELS	FINL	INFT	INFT	INDU	FINL	INFT	S&P	MATR	TELS
9	16.30%	-30.50%	27.10%	22.20%	11.40%	18.30%	35.60%	20.10%	5.90%	18.90%	22.20%	-0.30%	31.50%	20.70%	19.70%
10	CONS	COND	S&P	ENRS	TELS	HLTH	S&P	CONS	REAL	MATR	HLTH	REAL	INDU	S&P	INDU
11	14.20%	-33.50%	26.50%	20.50%	6.30%	17.90%	32.40%	16.00%	4.70%	16.70%	22.10%	-2.20%	29.40%	18.40%	16.40%
12	INDU	ENRS	INDU	TELS	COND	S&P	INFT	FINL	TELS	UTIL	S&P	S&P	REAL	HLTH	S&P
13	12.00%	-34.90%	20.90%	19.00%	6.10%	16.00%	28.40%	15.20%	3.40%	16.30%	21.80%	-4.40%	29.00%	13.50%	15.30%
14	TELS	S&P	HLTH	S&P	ENRS	INDU	CONS	S&P	S&P	INFT	INDU	CONS	COND	INDU	MATR
15	11.90%	-37.00%	19.70%	15.10%	4.70%	15.40%	26.10%	13.70%	1.40%	13.90%	21.00%	-8.40%	27.90%	11.10%	14.50%
16	HLTH	INDU	FINL	CONS	INFT	MATR	MATR	INDU	FINL	S&P	CONS	TELS	CONS	CONS	INFT
17	7.20%	-39.90%	17.20%	14.10%	2.40%	15.00%	25.60%	9.80%	-1.50%	12.00%	13.50%	-12.50%	27.60%	10.80%	13.80%
18	S&P	REAL	CONS	FINL	S&P	INFT	ENRS	COND	INDU	COND	UTIL	FINL	UTIL	UTIL	HLTH
19	5.50%	-42.30%	14.90%	12.10%	2.10%	14.80%	25.10%	9.70%	-2.50%	6.00%	12.10%	-13.00%	26.40%	0.50%	11.90%
20	COND	INFT	ENRS	INFT	INDU	CONS	UTIL	MATR	UTIL	CONS	REAL	INDU	MATR	FINL	COND
21	-13.20%	-43.10%	13.80%	10.20%	-0.60%	10.80%	13.20%	6.90%	-4.80%	5.40%	10.90%	-13.30%	24.60%	-1.70%	10.30%
22	REAL	MATR	UTIL	UTIL	MATR	ENRS	TELS	TELS	MATR	REAL	ENRS	MATR	HLTH	REAL	CONS
23	-17.90%	-45.70%	11.90%	5.50%	-9.60%	4.60%	11.50%	3.00%	-8.40%	3.40%	-1.00%	-14.70%	20.80%	-2.20%	5.00%
24	FINL	FINL	TELS	HLTH	FINL	UTIL	REAL	ENRS	ENRS	HLTH	TELS	ENRS	ENRS	ENRS	UTIL
25	-18.60%	-55.30%	8.90%	2.90%	-17.10%	1.30%	1.60%	-7.80%	-21.10%	-2.70%	-1.30%	-18.10%	11.80%	-33.70%	2.40%
Sheet1

I am trying to rearrange the data in the following format:

Book1
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
2	Abbr	Desc	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
3	INDU	S&P 500 Industrials Index
4	S&P	S&P 500 Index
5	INFT	S&P 500 Information Technology Index
6	ENRS	S&P 500 Energy Index
7	REAL	S&P 500 Real Estate Index
8	MATR	S&P 500 Materials Index
9	COND	S&P 500 Consumer Discretionary Index
10	TELS	S&P 500 Communication Services Index
11	FINL	S&P 500 Financials Index
12	UTIL	S&P 500 Utilities Index
13	CONS	S&P 500 Consumer Staples Index
14	HLTH	S&P 500 Health Care Index
Sheet1

I tried using xlookup but it did not work. Appreciate all the help.

 

[Andy B]

Enter Formula: =OFFSET(XLOOKUP($Q3,A:A,A:A),1,0) into cell S3 and copy across and down.

 

[Fluff]

Another option, without volatile functions

=INDEX(A$2:A$25,MATCH($Q2,A$2:A$25,0)+1)

 

[kumara_faith]

Hi Andy B and Fluff,

Thank you and appreciate your valuable time and patience. Both worked. Have a great day ahead.?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

Here is an alternative solution for you. Power Query also called Get and Transform Data and found on the Data Tab of the Ribbon

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Value	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
2	COND	-0.132	-0.335	0.413	0.277	0.061	0.239	0.431	0.097	0.101	0.06	0.23	0.008	0.279	0.333	0.103
3	CONS	0.142	-0.154	0.149	0.141	0.14	0.108	0.261	0.16	0.066	0.054	0.135	-0.084	0.276	0.108	0.05
4	ENRS	0.344	-0.349	0.138	0.205	0.047	0.046	0.251	-0.078	-0.211	0.274	-0.01	-0.181	0.118	-0.337	0.456
5	FINL	-0.186	-0.553	0.172	0.121	-0.171	0.288	0.356	0.152	-0.015	0.228	0.222	-0.13	0.321	-0.017	0.257
6	HLTH	0.072	-0.228	0.197	0.029	0.127	0.179	0.415	0.253	0.069	-0.027	0.221	0.065	0.208	0.135	0.119
7	INDU	0.12	-0.399	0.209	0.267	-0.006	0.154	0.407	0.098	-0.025	0.189	0.21	-0.133	0.294	0.111	0.164
8	INFT	0.163	-0.431	0.617	0.102	0.024	0.148	0.284	0.201	0.059	0.139	0.388	-0.003	0.503	0.439	0.138
9	MATR	0.225	-0.457	0.486	0.222	-0.096	0.15	0.256	0.069	-0.084	0.167	0.238	-0.147	0.246	0.207	0.145
10	REAL	-0.179	-0.423	0.271	0.323	0.114	0.197	0.016	0.302	0.047	0.034	0.109	-0.022	0.29	-0.022	0.233
11	S&P	0.055	-0.37	0.265	0.151	0.021	0.16	0.324	0.137	0.014	0.12	0.218	-0.044	0.315	0.184	0.153
12	TELS	0.119	-0.305	0.089	0.19	0.063	0.183	0.115	0.03	0.034	0.235	-0.013	-0.125	0.327	0.236	0.197
13	UTIL	0.194	-0.29	0.119	0.055	0.199	0.013	0.132	0.29	-0.048	0.163	0.121	0.041	0.264	0.005	0.024
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Value.Is([Value], type number) then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Up", "Custom.1", each if Value.Is([Value],type number) then null else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1", "Value", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Column1]), "Column1", "Custom")
in
    #"Pivoted Column"