Pull data from multiple sheets based off name and month selected

[Amo840812]

I have a workbook where I want to select an employee's name and a month to pull data matching those 2 criteria. Then I have metrics I want it to pull from the sheet that matches the employee's name and pull the data matching the month and metrics name.

Here is the sheet (named 1x1s) where I want to select the name and month and have the data generate:

In the same workbook are sheets named the employee's name (one sheet per employee) and it lists their metrics for each month:

So, for instance, in this example I would want cell D5 on the sheet named 1x1s to pull the metric for Metric1 from the sheet Employee 1 under column matching Jan. Is this possible? I know I could do a super messy if and formula for each employee for each month but there has got to be an easier way (I was thinking indirect or vlookup but not sure).

 

[Eric W]

Given this employee sheet:

Book1
	A	B	C	D
1
2	EE NAME	Goal	Jan	Feb
3	Metric1		1%
4	Metric2		2%
5	Metric3		3%
6	Metric4		4%
7	Metric5		5%
8	Metric6	51%	6%
9	Metric7		7%
10	Metric8		8%
11	Metric9		9%
12	Metric10		10%
13	Metric11		11%
14	Metric12		12%
15	Metric13		13%
16	Metric14		14%
17	Metric15	8%	15%
18	Metric16	80%	16%
19	Metric17		17%
20	Metric18		18%
21	Metric19		19%
22	Metric20		20%
Employee 1

You can do this:

Book1
	A	B	C	D	E
1
2		Select Employee:	Employee 1	Select month:	Jan
3
4		Metric	Goal	Individual	Team
5		Metric1	95%	1%
6		Metric6	40%	6%
7		Metric3	90%	3%
8		Metric11	88%	11%
9
1x1s

Cell Formulas
Range	Formula
D5:D8	D5	=LET(s,INDIRECT("'"&$C$2&"'!A2:N50"),INDEX(s,MATCH($B5:$B8,INDEX(s,0,1),0),MATCH($E$2,INDEX(s,1,0),0)))
Dynamic array formulas.

 

[Amo840812]

Thank you!!