Create formula to show an individual employee's schedule

[mjswenson84]

I have created a 12 month schedule in Excel. In column A are the different locations. In columns B-M are the individuals. In each B-M column an individual only appears once.
For instance:
In A2 is location alpha. Employee1 appears in B2 (July), employee2 appears in C2 (August), employee3 appears in D2 (September), etc.
In A3 is location beta. Employee3 appears in B3 (July), employee1 appears in C3 (August), employee2 appears in D3 (September), etc.

I now want to create a formula so that I can see where each employee works each month. I need to answer the question "where is employee1 in July, August, September, etc."
So I want a formula to look something like "when employee1 appears in column B, what is the text of column A in that same row"

This is for a large schedule with multiple locations and employees. I need to be able to see an individual employee's schedule for the year to make sure the schedule does not create undo burden on the employee during any given period

 

[awoohaw]

try:

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	General Surgery	5B	5C	5D	5A	5D	5C	5B	5A	4A	4B	4C	4D
3	General Surgery	3D	3E	3A	3B	3C	3E	3A	3C	3B	3A	3E	3D
4	General Surgery	1G	1K	1F	1J	1C	1H	1A	1B	1L	1D	1i	1E
5	General Surgery	1H	1L	1G	1K	1D	1A	1i	1C	1B	1E	1J	1F
6	General Surgery	T	T	T	T	T	T	T	T	T	T	T	T
8	Specialty	5C	5D	5A	5B	5A	5D	5C	5B	4C	4D	4A	4B
10	MIS	5D	5A	5B	5C	5B	5A	5D	5C	4D	4A	4B	4C
11	MIS	3B	3C	3D	3E	2D	2C	2B	2A	2B	2D	2A	2C
13	CVT	3E	3A	3B	3C	3D	3A	3B	3A	3E	3D	3C	3B
14	CVT	1i	1B	1D	1A	1E	1C	1J	1L	1H	1F	1K	1G
16	Nights	1B	1F	1L	1E	1J	1K	1H	1D	1i	1G	1A	1C
17
18
19	1A				CVT		General Surgery	General Surgery				Nights
20	1B	Nights	CVT						General Surgery	General Surgery
21	1C					General Surgery	CVT		General Surgery				Nights
22	1D			CVT		General Surgery			Nights		General Surgery
23	1E				Nights	CVT					General Surgery		General Surgery
24	1F		Nights	General Surgery							CVT		General Surgery
25	1G	General Surgery		General Surgery							Nights		CVT
26	1H	General Surgery					General Surgery	Nights		CVT
27	1i	CVT						General Surgery		Nights		General Surgery
28	1J				General Surgery	Nights		CVT				General Surgery
29	1K		General Surgery		General Surgery		Nights					CVT
30	1L		General Surgery	Nights					CVT	General Surgery
31	2A								MIS			MIS
32	2B							MIS		MIS
33	2C						MIS						MIS
34	2D					MIS					MIS
35	3A		CVT	General Surgery			CVT	General Surgery	CVT		General Surgery
36	3B	MIS		CVT	General Surgery			CVT		General Surgery			CVT
37	3C		MIS		CVT	General Surgery			General Surgery			CVT
38	3D	General Surgery		MIS		CVT					CVT		General Surgery
39	3E	CVT	General Surgery		MIS		General Surgery			CVT		General Surgery
40	4A									General Surgery	MIS	Specialty
41	4B										General Surgery	MIS	Specialty
42	4C									Specialty		General Surgery	MIS
43	4D									MIS	Specialty		General Surgery
44	5A		MIS	Specialty	General Surgery	Specialty	MIS		General Surgery
45	5B	General Surgery		MIS	Specialty	MIS		General Surgery	Specialty
46	5C	Specialty	General Surgery		MIS		General Surgery	Specialty	MIS
47	5D	MIS	Specialty	General Surgery		General Surgery	Specialty	MIS
48	T	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery	General Surgery
mjswenson84

Cell Formulas
Range	Formula
B1	B1	=DATE(2023,1,1)
C1:M1	C1	=EDATE(B1,1)
A19:A48	A19	=UNIQUE(SORT(TRANSPOSE(HSTACK(B4:M4,B5:M5,B6:M6,B8:M8,B10:M10,B11:M11,B13:M13,B14:M14,B16:M16))))
B19:M48	B19	=IFERROR(INDEX($A$2:$A$16,MATCH($A19,B$2:B$16,0)),"")
Dynamic array formulas.