Selective comparison

[tigerzen]

A number of tables are received each week and a variation table (I think there's a more official name for this but not sure what it is) is required. What's needed is a table indicating the differences in values between tables. Desired results are in rows 12 to 16, my initial attempt is in rows 6 to 10 but it doesn't account for the other table. I was thinking the CHOOSE function might be the most appropriate way to do this but couldn't quite get it to work. Looking for a formula based solution using Excel 2019.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Team 1	Jan	Feb	March		Team 2	Jan	Feb	March		Team 3	Jan	Feb	March		Team 4	Jan	Feb	March
2	Staff numbers	34	29	40		Staff numbers	17	20	21		Staff numbers	50	55	46		Staff numbers	55	35	44
3	Absences	4	5	10		Absences	2	3	8		Absences	10	7	6		Absences	6	8	0
4	Promotions	2	3	0		Promotions	1	3	2		Promotions	6	2	8		Promotions	3	5	3
5
6	Compare	Team 1	versus	Team 3
7		Jan	Feb	March
8	Staff numbers	34	29	40
9	Absences	4	5	10
10	Promotions	2	3	0
11
12	Compare	Team 1	versus	Team 3
13		Jan	Feb	March
14	Staff numbers	16	26	6
15	Absences	6	2	-4
16	Promotions	4	-1	8
Sheet6

Cell Formulas
Range	Formula
C8:E10	C8	=INDEX($B$1:$T$4,MATCH($B8,$B$1:$B$4,0),MATCH(C$7,$B$1:$T$1,0))

Cells with Data Validation
Cell	Allow	Criteria
C6	List	Team 1,Team 2,Team 3, Team 4
E6	List	Team 1,Team 2,Team 3, Team 4
C12	List	Team 1,Team 2,Team 3, Team 4
E12	List	Team 1,Team 2,Team 3, Team 4

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Team 1	Jan	Feb	March		Team 2	Jan	Feb	March		Team 3	Jan	Feb	March
2		Staff numbers	34	29	40		Staff numbers	17	20	21		Staff numbers	50	55	46
3		Absences	4	5	10		Absences	2	3	8		Absences	10	7	6
4		Promotions	2	3	0		Promotions	1	3	2		Promotions	6	2	8
5
6
7
8
9
10
11
12		Compare	Team 1	versus	Team 3
13			Jan	Feb	March
14		Staff numbers	16	26	6
15		Absences	6	2	-4
16		Promotions	4	-1	8
Sheet3

Cell Formulas
Range	Formula
C14:E16	C14	=INDEX($C$2:$Z$4,ROWS(C$14:C14),MATCH($E$12,$B$1:$Z$1,0)+COLUMNS($C14:C14)-1)-INDEX($C$2:$Z$4,ROWS(C$14:C14),MATCH($C$12,$B$1:$Z$1,0)+COLUMNS($C14:C14)-1)

Cells with Data Validation
Cell	Allow	Criteria
C12	List	Team 1,Team 2,Team 3,Team 4
E12	List	Team 1,Team 2,Team 3,Team 4

 

[awoohaw]

This is sort of clunky as you have to update the ABSOLUTE reference in each comparison block to capture the "area" number for the INDEX function.
But it works:

Mr excel questions 59.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Team 1	Jan	Feb	March		Team 2	Jan	Feb	March		Team 3	Jan	Feb	March		Team 4	Jan	Feb	March
2		Staff numbers	34	29	40		Staff numbers	17	20	21		Staff numbers	50	55	46		Staff numbers	55	35	44
3		Absences	4	5	10		Absences	2	3	8		Absences	10	7	6		Absences	6	8	0
4		Promotions	2	3	0		Promotions	1	3	2		Promotions	6	2	8		Promotions	3	5	3
5
6		Compare	Team 1	versus	Team 3
7			Jan	Feb	March
8		Staff numbers	16	26	6
9		Absences	6	2	-4
10		Promotions	4	-1	8
Sheet1

Cell Formulas
Range	Formula
C8:E10	C8	=INDEX(($B$1:$E$4,$G$1:$J$4,$L$1:$O$4,$Q$1:$T$4),MATCH($B8,$B$1:$B$4,0),MATCH(C$7,$B$1:$E$1,0),RIGHT($E$6,1))- INDEX(($B$1:$E$4,$G$1:$J$4,$L$1:$O$4,$Q$1:$T$4),MATCH($B8,$B$1:$B$4,0),MATCH(C$7,$B$1:$E$1,0),RIGHT($C$6,1))

 

[tigerzen]

Thanks so much Eric and Awoohaw, both solutions work admirably!