index+match

[kelvin_9]

Hi, i hope someone can help me out with my request listed below table
how can i use index+match to return "RDO" if date, name & RDO# met with left criteria?
it needs vba or just a formula can be done?

thanks all of your great help!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1		2		3		4		5		6		7
2	5/12/2020		4/12/2020		3/12/2020		2/12/2020		1/12/2020		6/12/2020		12/12/2020					8/12/2020	9/12/2020	10/12/2020
3	11/12/2020		10/12/2020		9/12/2020		8/12/2020		7/12/2020		19/12/2020		18/12/2020				RDO #	SCHEDULE	SCHEDULE	SCHEDULE
4	17/12/2020		16/12/2020		15/12/2020		14/12/2020		13/12/2020		25/12/2020		24/12/2020			Bee	2	10:00 - 19:30	11:30 - 21:00	RDO
5	23/12/2020		22/12/2020		21/12/2020		20/12/2020		26/12/2020		31/12/2020		30/12/2020			Brenda	4	RDO	09:15 - 18:45	11:30 - 21:00
6	29/12/2020		28/12/2020		27/12/2020											Brian	1		11:30 - 21:00	00:00 - 00:00
7																Carlie	5	09:15 - 18:45	09:15 - 18:45	11:30 - 21:00
8																Dave	3	09:15 - 18:45	RDO
9																Dickson	1	11:30 - 21:00
Sheet1

 

[Bobjg]

Question - Are you looking to MATCH the dates on the left to the corresponding date in R/2 C/R, S & T?

 

[kelvin_9]

Question - Are you looking to MATCH the dates on the left to the corresponding date in R/2 C/R, S & T?

yes, ugh...
T4/R5/R6/S8/T8/S9/T9, are just blank cells original, and i want to return "RDO", if:
1) either one date from R2:T2, are met with A:M
2) Q4:Q9, are met with A1:M1
3) return "RDO" in blank cell if correct

that is:
in cell T4: T2 met with C3, Q4 met with C1, return "RDO"
in cell R5: R2 met with G3, Q5 met with G1, return "RDO"... etc
in cell T8, despite T2 met with C3, Q8 dosent met with C1, return blank cell then... etc

i'm sorry for my poor english presenting, i hope you find what i'm talking

 

[kelvin_9]

can anyone give me a helping hand/opinion what should I start with?
thanks a lot!

 

[steve the fish]

So you just want to fill the blank cells? You cant do that with a formula unless you create a new table to look at the one with the times in it. Place this in V5 and copy across and down and you may see what i mean.

=IF(R5="",IF(ISNUMBER(MATCH(R$3,INDEX($A$3:$M$7,,MATCH($Q5,$A$2:$M$2,0)),0)),"RDO",""),R5)

 

[kelvin_9]

Thank you Steve for your reply, i've tried and work seems fine unless i amended some cells with your formula like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	1		2		3		4		5		6		7
2	5/12/2020		4/12/2020		3/12/2020		2/12/2020		1/12/2020		6/12/2020		12/12/2020					8/12/2020	9/12/2020	10/12/2020
3	11/12/2020		10/12/2020		9/12/2020		8/12/2020		7/12/2020		19/12/2020		18/12/2020				RDO #	SCHEDULE	SCHEDULE	SCHEDULE
4	17/12/2020		16/12/2020		15/12/2020		14/12/2020		13/12/2020		25/12/2020		24/12/2020			Bee	2	10:00 - 19:30	11:30 - 21:00			10:00 - 19:30	11:30 - 21:00	RDO
5	23/12/2020		22/12/2020		21/12/2020		20/12/2020		26/12/2020		31/12/2020		30/12/2020			Brenda	4		09:15 - 18:45	11:30 - 21:00		RDO	09:15 - 18:45	11:30 - 21:00
6	29/12/2020		28/12/2020		27/12/2020											Brian	1		11:30 - 21:00	00:00 - 00:00			11:30 - 21:00	00:00 - 00:00
7																Carlie	5	09:15 - 18:45	09:15 - 18:45	11:30 - 21:00		09:15 - 18:45	09:15 - 18:45	11:30 - 21:00
8																Dave	3	09:15 - 18:45				09:15 - 18:45	RDO
9																Dickson	1	11:30 - 21:00				11:30 - 21:00
RDO

Cell Formulas
Range	Formula
V4:X9	V4	=IF(R4="",IF(ISNUMBER(MATCH(R$2,INDEX($A$2:$M$10,,MATCH($Q4,$A$1:$M$1,0)),0)),"RDO",""),R4)

i understood what you mean i cant use it as normal unless a vba? or new table with your formula
however, i extract my employee schedule with below vba, can it combine with your formula so that one column at a time to return "time and rdo" together?
if not, i may need to add more column for myself and then use "=if(R4="",v4,r4)

Sub Macro002()
'
' Macro002 Macro
'

'sunday formula

    Sheets("RESULT-1").Select
    Dim rDate As Range, rDest As Range
    Dim sText1 As String, sText2 As String

    Set rDate = Range("c50")
    Set rDest = Range("c51")

    sText1 = "TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX('SCHEDULE-1'!$C:$C,MATCH('RESULT-1'!$A51,'SCHEDULE-1'!$A:$A,0)):INDEX('SCHEDULE-1'!$C:$C,MATCH('RESULT-1'!$A52,'SCHEDULE-1'!$A:$A,0)-1),5)),1)),""hh:mm"")"
    sText2 = "TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX('SCHEDULE-1'!$C:$C,MATCH('RESULT-1'!$A51,'SCHEDULE-1'!$A:$A,0)):INDEX('SCHEDULE-1'!$C:$C,MATCH('RESULT-1'!$A52,'SCHEDULE-1'!$A:$A,0)-1),7,5)),0)),""hh:mm"")"

    rDest.FormulaArray = "=IF(INDEX('SCHEDULE-1'!$C:$C,MATCH('RESULT-1'!$A51,'SCHEDULE-1'!$A:$A,0))="""","""",1111&"" - ""&2222)"
    rDest.Replace "1111", sText1, LookAt:=xlPart
    rDest.Replace "2222", sText2, LookAt:=xlPart

    Range("c51").AutoFill Destination:=Range("c51:c" & Range("a" & Rows.Count).End(xlUp).Row)

    Range("d51").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""00:00 - 00:00"",VLOOKUP(RC1,'SCHEDULE-1'!C1:C9,3,FALSE),"""")"
    Range("d51").AutoFill Destination:=Range("d51:d" & Range("a" & Rows.Count).End(xlUp).Row)
    
End Sub

thanks so much