Automated lookups without having to do multiple VLookups

[A123]

Hi all,

I am struggling to work out how to set up formulae to pull the figures marked by a ? in spreadsheet 1 as per example below. The Name in left hand column and Code name in top horizontal row match the same names on spreadsheet 2 which contains the answers. At present, I am doing a simple V-lookup for each Code but this means I have to do a V-lookup for every code which takes time.

I would love to know what formulae I can put it to Spreadsheet 1 to automatically pull the answer from Spreadsheet 2 (which is kept on a separate tab).

Thank you in advance for your help! It is much appreciated.

 

[myall_blues]

Try this for layout as shown. If you move the sheet 2 information formulas will update accordingly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1				Code 1	Code 2	Code 3	Code 4	Code 5	Code 6						Code 1	Code 2	Code 3	Code 4	Code 5	Code 6
2			Name 1	11	10	9	46	48	45					Name 1	11	10	9	46	48	45
3			Name 2	13	52	49	14	43	11					Name 2	13	52	49	14	43	11
4			Name 3	20	8	38	46	18	30					Name 3	20	8	38	46	18	30
5			Name 4	27	17	1	47	19	50					Name 4	27	17	1	47	19	50
6			Name 5	13	1	40	3	15	1					Name 5	13	1	40	3	15	1
7			Name 6	4	22	12	43	3	20					Name 6	4	22	12	43	3	20
8
Sheet1

Cell Formulas
Range	Formula
D2:I7	D2	=SUMPRODUCT(($N$2:$N$7=$C2)*($O$1:$T$1=D$1)*$O$2:$T$7)

 

[A123]

Try this for layout as shown. If you move the sheet 2 information formulas will update accordingly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1				Code 1	Code 2	Code 3	Code 4	Code 5	Code 6						Code 1	Code 2	Code 3	Code 4	Code 5	Code 6
2			Name 1	11	10	9	46	48	45					Name 1	11	10	9	46	48	45
3			Name 2	13	52	49	14	43	11					Name 2	13	52	49	14	43	11
4			Name 3	20	8	38	46	18	30					Name 3	20	8	38	46	18	30
5			Name 4	27	17	1	47	19	50					Name 4	27	17	1	47	19	50
6			Name 5	13	1	40	3	15	1					Name 5	13	1	40	3	15	1
7			Name 6	4	22	12	43	3	20					Name 6	4	22	12	43	3	20
8
Sheet1

Cell Formulas
Range	Formula
D2:I7	D2	=SUMPRODUCT(($N$2:$N$7=$C2)*($O$1:$T$1=D$1)*$O$2:$T$7)

Thanks so much. This seems to be working. Really appreciate your help.