Multiple lookups with date range

[ALRENDLE]

Hi,

The title of this thread may well be incorrect, as I'm really not sure what formula or possibly VBA code I require in order to do solve my issue.

I am creating a basic booking schedule in Excel and need to be able to do the following: Input a formula into the top table which looks at the bottom data table, matches the location and returns the job number into the top table if it falls within the specified date range. So in other words job number: CE-17/18-002 should appear in cells B4:F5. Job number: CE-17/18-001 should also appear in cells B8:F9. Any advice greatly welcome! Thank you

Booking Schedule:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]24/11/17[/TD]
[TD]25/11/17[/TD]
[TD]26/11/17[/TD]
[TD]27/11/17[/TD]
[TD]28/11/17[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Cab 12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cab 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cab 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cab 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Weiss 29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Weiss 30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Weiss 31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Weiss 32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Weiss 33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Job Number[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cab 14[/TD]
[TD]CE-17/18-002
[/TD]
[TD]06-Oct-17
[/TD]
[TD]31-Oct-17


[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cab 15[/TD]
[TD]CE-17/18-002
[/TD]
[TD]06-Oct-17


[/TD]
[TD]31-Oct-17


[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Weiss 31[/TD]
[TD]CE-17/18-001
[/TD]
[TD]24-Nov-17


[/TD]
[TD]24-Dec-17


[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Weiss 32[/TD]
[TD]CE-17/18-001
[/TD]
[TD]24-Nov-17


[/TD]
[TD]24-Dec-17


[/TD]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

"job number: CE-17/18-002 should appear in cells B4:F5. "

Nope it shouldnt, since the start and end dates in the Data table are in October but all the Booking Schedule dates are in November so they dont fall in the date range.

 

[Special-K99]

in 'Booking Schedule'!B2
=IFERROR(LOOKUP(2,1/($A2='Data Table'!$A$2:$A$5)/(B$1>='Data Table'!$C$2:$C$5)/(B$1<='Data Table'!$D$2:$D$5),'Data Table'!$B$2:$B$5),"")
copy across and down to fill out the Booking Schedule table

 

[ALRENDLE]

Oh my goodness, thank you so much! I have spent so much time trying to work this out! Cannot thank you enough

 

[ALRENDLE]

Yes, spotted that right after I posted thank you. Sorry for the confusion.