Multiple lookups with date range

ALRENDLE

New Member
Joined
Apr 4, 2018
Messages
3
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:
ABCDEF
124/11/1725/11/1726/11/1727/11/1728/11/17
2
Cab 12
3Cab 13
4Cab 14
5Cab 15
6Weiss 29
7Weiss 30
8Weiss 31
9Weiss 32
10Weiss 33

<tbody>
</tbody>

Data table:
ABCDE
1LocationJob NumberStart DateEnd DateDuration
2Cab 14CE-17/18-002
06-Oct-17
31-Oct-17


26
3Cab 15CE-17/18-002
06-Oct-17


31-Oct-17


26
4Weiss 31CE-17/18-001
24-Nov-17


24-Dec-17


31
5Weiss 32CE-17/18-001
24-Nov-17


24-Dec-17


31

<tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
"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.
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
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

New Member
Joined
Apr 4, 2018
Messages
3
Oh my goodness, thank you so much! I have spent so much time trying to work this out! Cannot thank you enough :)
 

ALRENDLE

New Member
Joined
Apr 4, 2018
Messages
3
Yes, spotted that right after I posted :eek: thank you. Sorry for the confusion.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,972
Messages
5,767,393
Members
425,410
Latest member
SmittyT

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