Index+Match

[kelvin_9]

hello all
i am wondering can i use index+match to return column L into my table O2:V11?

thank you very much for your guidence

Sample.xlsx
	A	B	C	D	E	F				J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Worker Type	LOB	Location	Person No.	Name	Date				Start Time	End Time
2					Holiday	07/28/2024				7:45	17:15	A			Name	28-Jul	29-Jul	30-Jul	31-Jul	1-Aug	2-Aug	3-Aug
3					Carol	07/29/2024				13:45	23:15	B				Sun	Mon	Tue	Wed	Thu	Fri	Sat
4					Catherine	07/30/2024				22:45	8:15	C			Holiday	A
5					Ming	07/31/2024				21:30	7:00	C1			Carol		B
6					Marcus	08/01/2024				9:00	15:45	A1			Catherine			C
7					Toru	08/02/2024				16:30	23:15	B1			Ming				C1
8					Thetis	08/03/2024				7:45	17:15	A			Marcus					A1
9					Amen	07/28/2024				13:45	23:15	B			Toru						B1
10															Thetis							A
11															Amen	B
136

Cell Formulas
Range	Formula
L2:L9	L2	=IFERROR(INDEX({"A";"B";"C";"C1";"A1";"B1"}, MATCH(TEXT(J2, "h:mm")&"-"&TEXT(K2, "h:mm"),{"7:45-17:15";"13:45-23:15";"22:45-8:15";"21:30-7:00";"9:00-15:45";"16:30-23:15"},0)), "not defined")

 

[etaf]

try
=IFERROR(INDEX($L$2:$L$9,MATCH(P$2&$O4,$F$2:$F$9&$E$2:$E$9,0)),"")

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Worker Type	LOB	Location	Person No.	Name	Date				Start Time	End Time
2					Holiday	7/28/24				0.32291667	0.71875	A			Name	7/28/24	7/29/24	7/30/24	7/31/24	8/1/24	8/2/24	8/3/24
3					Carol	7/29/24				0.57291667	0.96875	B				Sun	Mon	Tue	Wed	Thu	Fri	Sat
4					Catherine	7/30/24				0.94791667	0.34375	C			Holiday	A
5					Ming	7/31/24				0.89583333	0.29166667	C1			Carol		B
6					Marcus	8/1/24				0.375	0.65625	A1			Catherine			C
7					Toru	8/2/24				0.6875	0.96875	B1			Ming				C1
8					Thetis	8/3/24				0.32291667	0.71875	A			Marcus					A1
9					Amen	7/28/24				0.57291667	0.96875	B			Toru						B1
10															Thetis							A
11															Amen	B
Sheet1

Cell Formulas
Range	Formula
P4:V11	P4	=IFERROR(INDEX($L$2:$L$9,MATCH(P$2&$O4,$F$2:$F$9&$E$2:$E$9,0)),"")
L2:L9	L2	=IFERROR(INDEX({"A";"B";"C";"C1";"A1";"B1"}, MATCH(TEXT(J2, "h:mm")&"-"&TEXT(K2, "h:mm"),{"7:45-17:15";"13:45-23:15";"22:45-8:15";"21:30-7:00";"9:00-15:45";"16:30-23:15"},0)), "not defined")

 

[Eric W]

How about:

Book3
	O	P	Q	R	S	T	U	V
2	Name	7/28/2024	7/29/2024	7/30/2024	7/31/2024	8/1/2024	8/2/2024	8/3/2024
3		Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	Holiday	A
5	Carol		B
6	Catherine			C
7	Ming				C1
8	Marcus					A1
9	Toru						B1
10	Thetis							A
11	Amen	B
Sheet1

Cell Formulas
Range	Formula
P4:V11	P4	=IFERROR(INDEX($L:$L,AGGREGATE(15,6,ROW($E$2:$E$9)/($E$2:$E$9=$O4)/($F$2:$F$9=P$2),1)),"")

 

[kelvin_9]

thank you for your reply, etaf & Eric W

both are excellent, made my day

thank you very much

 

[etaf]

you are welcome