Transposing data that is in a table, to columns

[nathangwynmorris]

Hi All,

This has been annoying me for far too long now, I really need some help. If someone has a solution for excel and google sheets, I would really appreciate it.

In short, I have a list of the country, jobcode and the level (every eventuality) and then what I expect in column n is for it to concatenate the country, job code and level and then look at the table on the left and pull the value. I think it needs to be index match but I really cannot get my head around it.

Probably very simple to some, but yeah - I cannot do it. Thanks in advance as always!

 

[Cubist]

Another option.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			0	1	2	3	4	5	6	7	8
2	W	A		812	309	727	618	311	134	238	235		W	A	0
3	W	B	480	275	147	690		723	227	347	156		W	A	1	812
4	W	C	604	699	618	216	926	748	258	811	432		W	A	2	309
5	W	D	213	178	291	425	939	379	898	232	867		W	A	3	727
6	W	E	737	990	918	744	523	425	695	876	105		W	A	4	618
7	W	F	988	633	184	677	503	551	541	486	293		W	A	5	311
8	W	G	452	377	303	126	394	775	699	127	861		W	A	6	134
9	W	H	457	962	805	560	907	487	187	970	903		W	A	7	238
10													W	A	8	235
11			0	1	2	3	4	5	6	7	8		W	B	0	480
12	E	K		812	309	727	618	311	134	238	235		W	B	1	275
13	E	L	480	275	147	690		723	227	347	156		W	B	2	147
14	E	M	604	699	618	216	926	748	258	811	432		W	B	3	690
15	E	N	213	178	291	425	939	379	898	232	867		W	B	4
16	E	O	737	990	918	744	523	425	695	876	105		W	B	5	723
17	E	P	988	633	184	677	503	551	541	486	293		W	B	6	227
18	E	Q	452	377	303	126	394	775	699	127	861		W	B	7	347
19	E	R	1	2	3	4	5	6	7	8	9		W	B	8	156
20													W	C	0	604
21													W	C	1	699
Sheet1

Cell Formulas
Range	Formula
M2:P145	M2	=LET(t,TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A2:A19&"|"&B2:B19& "|" & C1:K1 &"|" &C2:K19)),"|",","),FILTER(t,CHOOSECOLS(t,1)<>""))
Dynamic array formulas.