index+match

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
259
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!(y)

Book1
ABCDEFGHIJKLMNOPQRST
11234567
25/12/20204/12/20203/12/20202/12/20201/12/20206/12/202012/12/20208/12/20209/12/202010/12/2020
311/12/202010/12/20209/12/20208/12/20207/12/202019/12/202018/12/2020RDO #SCHEDULESCHEDULESCHEDULE
417/12/202016/12/202015/12/202014/12/202013/12/202025/12/202024/12/2020Bee210:00 - 19:3011:30 - 21:00RDO
523/12/202022/12/202021/12/202020/12/202026/12/202031/12/202030/12/2020Brenda4RDO09:15 - 18:4511:30 - 21:00
629/12/202028/12/202027/12/2020Brian111:30 - 21:0000:00 - 00:00
7Carlie509:15 - 18:4509:15 - 18:4511:30 - 21:00
8Dave309:15 - 18:45RDO
9Dickson111:30 - 21:00
Sheet1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Bobjg

New Member
Joined
Dec 11, 2019
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Question - Are you looking to MATCH the dates on the left to the corresponding date in R/2 C/R, S & T?
 

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
259
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

Active Member
Joined
Mar 6, 2015
Messages
259
can anyone give me a helping hand/opinion what should I start with?
thanks a lot!:oops:
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 6, 2015
Messages
259
Thank you Steve for your reply, i've tried and work seems fine unless i amended some cells with your formula like:
Book1
ABCDEFGHIJKLMNOPQRSTUVWX
11234567
25/12/20204/12/20203/12/20202/12/20201/12/20206/12/202012/12/20208/12/20209/12/202010/12/2020
311/12/202010/12/20209/12/20208/12/20207/12/202019/12/202018/12/2020RDO #SCHEDULESCHEDULESCHEDULE
417/12/202016/12/202015/12/202014/12/202013/12/202025/12/202024/12/2020Bee210:00 - 19:3011:30 - 21:0010:00 - 19:3011:30 - 21:00RDO
523/12/202022/12/202021/12/202020/12/202026/12/202031/12/202030/12/2020Brenda409:15 - 18:4511:30 - 21:00RDO09:15 - 18:4511:30 - 21:00
629/12/202028/12/202027/12/2020Brian111:30 - 21:0000:00 - 00:00 11:30 - 21:0000:00 - 00:00
7Carlie509:15 - 18:4509:15 - 18:4511:30 - 21:0009:15 - 18:4509:15 - 18:4511:30 - 21:00
8Dave309:15 - 18:4509:15 - 18:45RDO 
9Dickson111:30 - 21:0011:30 - 21:00  
RDO
Cell Formulas
RangeFormula
V4:X9V4=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)

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,662
Messages
5,637,637
Members
416,977
Latest member
kdoederlein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top