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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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.
 

Forum statistics

Threads
1,148,293
Messages
5,745,921
Members
423,984
Latest member
sayed manzar

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