index+match

[kelvin_9]

Hi everyone, i would like to use a index+match formula to extract when match with my criteria listed below table
=INDEX($H$1:$J$1,MATCH($G$3:$G$5,$A$2:$E$6,0))

		SUN	MON	TUE			IC	Recovery	Lead
Bee		10:00-19:30 HH - Recvory	11:30-21:00 HH - Recvory				SUN	MON	TUE
Brenda		09:15-09:45 CR	11:30-21:00 HH - IC	09:15-09:45 CR		Bee	Recvory	Recvory
		09:45-18:45 HH		09:45-14:00 HH - CART		Brenda		IC
				14:00-18:45 HH		Jean		Lead	Lead
Jean			10:00-19:30 Lead	11:30-20:15 Lead

thanks so much everyone for your help

 

[hernantorres23]

Hi, I' would pleasure for help you, but please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares

Thanks a lot

 

[kelvin_9]

thanks for your reply and i am sorry i am not really get what is XL2BB
but i guess if i upload this and that would be good for you.
ps. to avoid any mistake/enhance the accuracy, i think index+match+match will be more prefect like:
cell L2: =INDEX(MATCH($L$1:$N$1,MATCH($K3,$A$2:$A$6,0)),MATCH($L2,C1:I1,0))

http://kel.ddns.net/f/635a945018/?raw=1

thanks so much for your help

 

[kelvin_9]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			22/12/2019	23/12/2019	24/12/2019	25/12/2019	26/12/2019	27/12/2019	28/12/2019			IC	Recovery	Lead
2	Bee		10:00-19:30 HH - Recovery	11:30-21:00 HH - Recovery		09:15-09:45 CR	09:45-14:00 HH-Cart					22/12/2019	23/12/2019	24/12/2019	25/12/2019	26/12/2019	27/12/2019	28/12/2019
3	Brenda			09:15-09:45 CR	11:30-21:00 HH - IC	10:00-19:30 HH - Stroller		11:30-20:15 HH - Photo	09:15-09:45 CR		Bee	#N/A	Recovery
4				09:45-18:45 HH				20:15-21:00 CR	09:45-14:00 HH-Cart		Brenda			IC
5									14:00-18:45 HH		Jean			Lead			Lead	Lead
6	Jean				10:00-19:30 DD/Lead		11:30-20:15 HH - Photo	10:00-19:30 DD/Lead	11:30-21:00 MSS - Lead
Sheet1

Cell Formulas
Range	Formula
L3	L3	=INDEX(MATCH($L$1:$N$1,MATCH($K3,$A$2:$A$6,0)),MATCH($L2,C1:I1,0))

i did it, thanks for you help
ps. cell L3 should be Recovery if formula return correctly.

 

[FormR]

Hi, here is one option if I've understood correctly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			22/12/2019	23/12/2019	24/12/2019	25/12/2019	26/12/2019	27/12/2019	28/12/2019			IC	Recovery	Lead
2	Bee		10:00-19:30 HH - Recovery	11:30-21:00 HH - Recovery		09:15-09:45 CR	09:45-14:00 HH-Cart					22/12/2019	23/12/2019	24/12/2019	25/12/2019	26/12/2019	27/12/2019	28/12/2019
3	Brenda			09:15-09:45 CR	11:30-21:00 HH - IC	10:00-19:30 HH - Stroller		11:30-20:15 HH - Photo	09:15-09:45 CR		Bee	Recovery	Recovery
4				09:45-18:45 HH				20:15-21:00 CR	09:45-14:00 HH-Cart		Brenda			IC
5									14:00-18:45 HH		Jean			Lead			Lead	Lead
6	Jean				10:00-19:30 DD/Lead		11:30-20:15 HH - Photo	10:00-19:30 DD/Lead	11:30-21:00 MSS - Lead
Sheet1

Cell Formulas
Range	Formula
L3:R5	L3	=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($L$1:$N$1,INDEX($C$2:$I$6,MATCH($K3,$A$2:$A$6,0),MATCH(L$2,$C$1:$I$1,0)))),$L$1:$N$1),"")

 

[kelvin_9]

22/12/201923/12/201924/12/201925/12/201926/12/201927/12/201928/12/201

hi, you have nothing wrong and totally what i want by my poor english presentation.
THANKS SO MUCH EVERYONE HERE!!!