INDEX / MATCH Formula with multiple conditions

K

KungFu Keyboard

New Member
Joined
Oct 22, 2016
Messages
26
Hi there,

I have 2 data sets:

Dataset 1 (Flight Table)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Flight Number[/TD]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Departure Time[/TD]
[TD]Arrival Time[/TD]
[/TR]
[TR]
[TD]19020[/TD]
[TD]05/10/2017[/TD]
[TD]Wednesday[/TD]
[TD](formula)[/TD]
[TD](formula)[/TD]
[/TR]
</tbody>[/TABLE]


Dataset 2 (Flight Schedule)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Flight number[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Departure Time[/TD]
[TD]Arrival Time[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[/TR]
[TR]
[TD]19020[/TD]
[TD]01/10/2017[/TD]
[TD]10/10/2017[/TD]
[TD]07:20[/TD]
[TD]16:00[/TD]
[TD]Monday[/TD]
[TD][/TD]
[TD]Wednesday[/TD]
[TD][/TD]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19020[/TD]
[TD]03/10/2018[/TD]
[TD]12/10/2017[/TD]
[TD]07:45[/TD]
[TD]16:30[/TD]
[TD][/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[TD]Thursday[/TD]
[TD][/TD]
[TD]Saturday[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to pull the DEPARTURE and ARRIVAL TIME from the FLIGHT SCHEDULE into the FLIGHT TABLE based on whether the DATE in the FLIGHT TABLE falls within the range between START DATE and END DATE in the FLIGHT SCHEDULE.

The above is simple to solve and the following formula was used:

INDEX('FLIGHT SCHEDULE'!$D:$D,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!A2)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!B2)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!B2))

Confirming the actual day is a bit more challenging though as it requires a reference across the 7 columns (Monday - Sunday) in the FLIGHT SCHEDULE. I tried using a nested IF formula that references the day in the FLIGHT TABLE and does the same index match in the column for that day e.g. (The cell references below are from the actual dataset)

=IF(AB3="Monday",INDEX('FLIGHT SCHEDULE'!$F:$F,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!B3)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!D3)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!D3)*('FLIGHT SCHEDULE'!N:N=FLIGHTS!AB3),0)),IF(AB3="Tuesday",INDEX('FLIGHT SCHEDULE'!$F:$F,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!B3)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!D3)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!D3)*('FLIGHT SCHEDULE'!O:O=FLIGHTS!AB3),0)),IF(AB3="Wednesday",INDEX('FLIGHT SCHEDULE'!$F:$F,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!B3)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!D3)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!D3)*('FLIGHT SCHEDULE'!P:P=FLIGHTS!AB3),0)),IF(AB3="Thursday",INDEX('FLIGHT SCHEDULE'!$F:$F,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!B3)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!D3)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!D3)*('FLIGHT SCHEDULE'!Q:Q=FLIGHTS!AB3),0)),IF(AB3="Friday",INDEX('FLIGHT SCHEDULE'!$F:$F,MATCH(1,('FLIGHT SCHEDULE'!A:A=FLIGHTS!B3)*('FLIGHT SCHEDULE'!B:B<=FLIGHTS!D3)*('FLIGHT SCHEDULE'!C:C>=FLIGHTS!D3)*('FLIGHT SCHEDULE'!R:R=FLIGHTS!AB3),0)))))))

The problem is I can only use 5 conditions and then excel starts giving error messages. Is there a better way to do this?

Regards,
Bernard
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
This seems to work for me:


Book1
ABCDEFGHIJKL
1Flight NumberStart DateEnd DateDeparture TimeArrival TimeMondayTuesdayWednesdayThursdayFridaySaturdaySunday
21902001/10/201710/10/201707:2016:00MondayWednesdayFriday
31902003/10/201712/10/201707:4516:30TuesdayThursdaySaturday
FLIGHT SCHEDULE



Book1
ABCDE
1Flight NumberDateDayDeparture TimeArrival Time
21902004/10/2017Wednesday07:20:0016:00:00
31902005/10/2017Thursday07:45:0016:30:00
FLIGHTS
Cell Formulas
RangeFormula
C2=TEXT($B2,"dddd")
D2{=INDEX('FLIGHT SCHEDULE'!$D:$D,MATCH(1,('FLIGHT SCHEDULE'!$A:$A=$A2)*('FLIGHT SCHEDULE'!$B:$B<=$B2)*('FLIGHT SCHEDULE'!$C:$C>=$B2)*(INDEX('FLIGHT SCHEDULE'!$F:$L,,VLOOKUP($C2,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,FALSE))=$C2),0))}
E2{=INDEX('FLIGHT SCHEDULE'!$E:$E,MATCH(1,('FLIGHT SCHEDULE'!$A:$A=$A2)*('FLIGHT SCHEDULE'!$B:$B<=$B2)*('FLIGHT SCHEDULE'!$C:$C>=$B2)*(INDEX('FLIGHT SCHEDULE'!$F:$L,,VLOOKUP($C2,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,FALSE))=$C2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.


This assumes that column C is a text value and not a date formatted as "dddd".

WBD
 
Upvote 0
Using your example:

=INDEX('Flight Schedule'!D:D,MATCH(1,('Flight Schedule'!$A:$A=$A2)*('Flight Schedule'!$B:$B<=$B2)*('Flight Schedule'!$C:$C>=$B2)*(INDEX('Flight Schedule'!$F:$L,,MATCH($C2,'Flight Schedule'!$F$1:$L$1,0))=$C2),0))

You probably shouldnt use full column references though. Might prove to slow the workbook down.
 
Upvote 0
wideboydixon said:
This seems to work for me:

ABCDEFGHIJKL
Flight NumberStart DateEnd DateDeparture TimeArrival TimeMondayTuesdayWednesdayThursdayFridaySaturdaySunday
MondayWednesdayFriday
TuesdayThursdaySaturday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]19020[/TD]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]10/10/2017[/TD]
[TD="align: right"]07:20[/TD]
[TD="align: right"]16:00[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]19020[/TD]
[TD="align: right"]03/10/2017[/TD]
[TD="align: right"]12/10/2017[/TD]
[TD="align: right"]07:45[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
FLIGHT SCHEDULE​



ABCDE
Flight NumberDateDayDeparture TimeArrival Time
Wednesday
Thursday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]19020[/TD]
[TD="align: right"]04/10/2017[/TD]

[TD="align: right"]07:20:00[/TD]
[TD="align: right"]16:00:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]19020[/TD]
[TD="align: right"]05/10/2017[/TD]

[TD="align: right"]07:45:00[/TD]
[TD="align: right"]16:30:00[/TD]

</tbody>
FLIGHTS​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=TEXT($B2,"dddd")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=INDEX('FLIGHT SCHEDULE'!$D:$D,MATCH(1,('FLIGHT SCHEDULE'!$A:$A=$A2)*('FLIGHT SCHEDULE'!$B:$B<=$B2)*('FLIGHT SCHEDULE'!$C:$C>=$B2)*(INDEX('FLIGHT SCHEDULE'!$F:$L,,VLOOKUP($C2,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,FALSE))=$C2),0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=INDEX('FLIGHT SCHEDULE'!$E:$E,MATCH(1,('FLIGHT SCHEDULE'!$A:$A=$A2)*('FLIGHT SCHEDULE'!$B:$B<=$B2)*('FLIGHT SCHEDULE'!$C:$C>=$B2)*(INDEX('FLIGHT SCHEDULE'!$F:$L,,VLOOKUP($C2,{"Monday",1;"Tuesday",2;"Wednesday",3;"Thursday",4;"Friday",5;"Saturday",6;"Sunday",7},2,FALSE))=$C2),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This assumes that column C is a text value and not a date formatted as "dddd".

WBD
Click to expand...


Thanks for that - works perfectly :)
 
Upvote 0
Hi,

Sorry to be a bother, but although the formula works perfectly, for some reason it doesn't work in the macro I'm creating. The code in VBA as follows:

"=INDEX('FLIGHT SCHEDULE'!C6,MATCH(1,('FLIGHT SCHEDULE'!C[-22]=FLIGHTS!RC[-21])*('FLIGHT SCHEDULE'!C[-21]<=FLIGHTS!RC[-19])*('FLIGHT SCHEDULE'!C[-20]>=FLIGHTS!RC[-19])*(INDEX('FLIGHT SCHEDULE'!C14:C20,,VLOOKUP(FLIGHTS!RC[5],{""Monday"",1;""Tuesday"",2;""Wednesday"",3;""Thursday"",4;""Friday"",5;""Saturday"",6;""Sunday"",7},2,FALSE))=FLIGHTS!RC[5]),0))"

Why would the above not work in VBA?

Regards,
Bernard
 
Upvote 0
The error is:

run time error 1004 unable to set the formulaarray property of the range class

I see this has something to do with the length of the formula - how do I shorten it?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Dynamic calendar for parenting time
2
Replies
14
Views
990
felixstraube
F
J
  • Question Question
Formula for weekdays of date in another cell
Replies
4
Views
1K
Jaxi
J
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
345
AYouQueTai
A
D
  • Solved
VLOOKUP or INDEX/MATCH utilizing day of the week
Replies
5
Views
437
Darren_workforce
D
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
661
awoohaw
awoohaw

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

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
Back
Top