# index+match

#### kelvin_9

##### Active Member
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
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Bobjg

##### New Member
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
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
can anyone give me a helping hand/opinion what should I start with?
thanks a lot!

#### steve the fish

##### Well-known Member
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
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

Replies
3
Views
163
Replies
3
Views
92
Replies
0
Views
236
Replies
9
Views
72
Replies
1
Views
100

1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

### 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.

### Which adblocker are you using?

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

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