VLookup to return value within date range from list of multiple possible matches

P

PaulinhoC

New Member
Joined
Sep 15, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi peoples,

Needing some expertise here from someone much smarter than me.

I have a worksheet (named Usage) I have the list of Asset No. (column B) which details out the different periods they were hired out, Hire_Start (Column I) to Hire_End (Column J). The same asset no. can appear in multiple rows but with different hire periods.

1652405342747.png


On a separate worksheet (named Billable) that includes a Transaction Date (column B) and an Asset No. (column I)

1652404411776.png


I know how to do basic vlookups but when looking up the "Usage" sheet to return the Hire Start & End dates it always returns the first matching value (which I've set as the Asset No.).

Can anyone help with a formula so that I lookup the Asset No. to match and then only return the values of the Hire Start and End dates from the same row IF the "Transaction Date" (Column B) falls between the Hire Start (I) & End (J) dates stated within the "Usage" worksheet.

Thanking you all in advance.

Paulinho
 

Attachments

  • 1652404856939.png
    1652404856939.png
    43.5 KB · Views: 17

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
What is the VLOOKUP criteria? I could not find any connection between the two sheets
 
Upvote 0
Column B in 1st sheet is alphanumeric text vs column I in 2nd sheet is numeric, like M124541 vs 241160
How they connect together?
 
Upvote 0
bebo021999 said:
Column B in 1st sheet is alphanumeric text vs column I in 2nd sheet is numeric, like M124541 vs 241160
How they connect together?
Click to expand...

From the small sample I've screenshotted they don't appear to match but for some bizarre reason our asset no's have been labelled without a defined format. Some are numeric whilst other alpha-numeric, examples of more data from both sheet shows only some of the formats.

1652409056373.png


1652408825237.png
 
Upvote 0
bebo021999 said:
Try to post a mini sheet via: XL2BB - Excel Range to BBCode
so that helpers do not have to re -type the data on worksheet
Click to expand...

I hope this helps, sorry for being such a noob at this.

The "Usage" mini sheet

Shell charges April 2022 usage for May 22 Billing All.xlsx
ABCDEFGHIJ
1Maximo JH015 usage report March and April 22.xlsxAssetAsset DescriptionUsage MeterUsage Measurement DateFinancial PeriodCustomerNameHire_StartHire_End
2Maximo JH015 usage report March and April 22.xlsxM124541LEASED - Toyota Hilux DC Hi-Rider CF30YEM124541-DAILY29-Mar-22202203000001186069444John Holland Pty Ltd - Corporate St Kilda25/02/202229/03/2022
3Maximo JH015 usage report March and April 22.xlsxM124541LEASED - Toyota Hilux DC Hi-Rider CF30YEM124541-MONTHLY-HIRE27-Apr-22202204000001186069444John Holland Pty Ltd - Corporate St Kilda30/03/202227/04/2022
4Maximo JH015 usage report March and April 22.xlsxM132802Toyota Hilux 2.8 T/Diesel SR Dual Cab 4x4 Auto with Canopy- 1LV7RTM132802-MONTHLY-HIRE27-Apr-22202204000001186069444John Holland Pty Ltd - Corporate St Kilda30/03/202227/04/2022
5Maximo JH015 usage report March and April 22.xlsxM204069Veh4,DC Ute,Toyota,Hilux SR-825ZXXM204069__DAILY29-Mar-22202203000001186069444John Holland Pty Ltd - Corporate St Kilda25/02/202229/03/2022
6Maximo JH015 usage report March and April 22.xlsxM204069Veh4,DC Ute,Toyota,Hilux SR-825ZXXM204069-MONTHLY-HIRE27-Apr-22202204000001186069444John Holland Pty Ltd - Corporate St Kilda30/03/202227/04/2022
7Maximo JH015 usage report March and April 22.xlsxLV5811Veh4,S/Wgn,Toyota,Prado GXL-CD40QHLV5811-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
8Maximo JH015 usage report March and April 22.xlsxM205067Veh4,S/Wgn,Toyota,Prado GXL-CE26BZM205067-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
9Maximo JH015 usage report March and April 22.xlsxM206853LEASED - Toyota Prado 4x4 DC82UP TMM206853-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
10Maximo JH015 usage report March and April 22.xlsxM206853LEASED - Toyota Prado 4x4 DC82UP TMM206853-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
11Maximo JH015 usage report March and April 22.xlsxM206859LEASED - Toyota Prado 4x4 DC92UP TMM206859-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
12Maximo JH015 usage report March and April 22.xlsxM208627Toyota Prado - 2.8L GXL 4x4 WagonM208627-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
13Maximo JH015 usage report March and April 22.xlsxM128369LEASED - Toyota Hilux DC 4x4 CH72NAM128369-DAILY20-Mar-22202203000001186072117John Holland Infrastructure25/02/202220/03/2022
14Maximo JH015 usage report March and April 22.xlsxM128973LEASED - Toyota Hilux DC 4x4 CI82FLM128973-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
15Maximo JH015 usage report March and April 22.xlsxM129486Toyota Hilux DC 4x4 - CJ54HPM129486-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
16Maximo JH015 usage report March and April 22.xlsxM130425LEASED - Toyota Hilux DC 4x4 CK26WSM130425-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
17Maximo JH015 usage report March and April 22.xlsxM130705LEASED Toyota Hilux DC 2.8 T/Diesel - 1JY9RYM130705-DAILY11-Mar-22202203000001186072117John Holland Infrastructure25/02/202211/03/2022
18Maximo JH015 usage report March and April 22.xlsxM131723LEASED - Toyota Prado 4x4 CM58XU TMM131723-DAILY29-Mar-22202203000001186072117John Holland Infrastructure21/03/202229/03/2022
19Maximo JH015 usage report March and April 22.xlsxM131793LEASED - Toyota Hilux DC 4x4 CM54XUM131793-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
20Maximo JH015 usage report March and April 22.xlsxM132838LEASED - Toyota Hilux DC 4x4 CO94UJM132838-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
21Maximo JH015 usage report March and April 22.xlsxM132839LEASED - Toyota Hilux DC 4x4 CO92UJ TMM132839-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
22Maximo JH015 usage report March and April 22.xlsxM132897LEASED - Toyota Prado 4x4 CP81MK TMM132897-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
23Maximo JH015 usage report March and April 22.xlsxM132954Toyota Hilux DC 4x4 (CP91MK) CZ98EW TMM132954-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
24Maximo JH015 usage report March and April 22.xlsxM133133Veh4,S/Wgn,Toyota,Prado GXL-772YAWM133133-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
25Maximo JH015 usage report March and April 22.xlsxM133289Toyota Hilux DC 4x4 Hard Top CP13MLM133289-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
26Maximo JH015 usage report March and April 22.xlsxM133290Toyota Hilux DC 4x4 CP20ML TMM133290-DAILY24-Feb-22202203000001186072117John Holland Infrastructure22/02/202224/02/2022
27Maximo JH015 usage report March and April 22.xlsxM133290Toyota Hilux DC 4x4 CP20ML TMM133290-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
28Maximo JH015 usage report March and April 22.xlsxM133291Toyota Hilux DC 4x4 CR53RZM133291-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
29Maximo JH015 usage report March and April 22.xlsxM133362LEASED - Toyota Prado 4x4 CP10UJM133362-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
30Maximo JH015 usage report March and April 22.xlsxM133763Toyota Hilux DC 4x4 CQ31KGM133763-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
31Maximo JH015 usage report March and April 22.xlsxM134109LEASED - Toyota Prado 4x4 CR58EGM134109-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
32Maximo JH015 usage report March and April 22.xlsxM134167Toyota Hilux DC 4x4 CQ43WT TMM134167-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
33Maximo JH015 usage report March and April 22.xlsxM134233LEASED - Toyota Hilux SR5 DC 4x4 CR60EG TMM134233-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
34Maximo JH015 usage report March and April 22.xlsxM134574LEASED - Toyota Prado 4x4 CR73EG TMM134574-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
35Maximo JH015 usage report March and April 22.xlsxM135643LEASED - Toyota Prado 4x4 CS24SB TMM135643-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
36Maximo JH015 usage report March and April 22.xlsxM136269Toyota Hilux DC 4x4 CT29RNM136269-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
37Maximo JH015 usage report March and April 22.xlsxM136442Nissan Navara MY18 T/Diesel 4x4 Auto with Canopy - 1PK1UPM136442-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
38Maximo JH015 usage report March and April 22.xlsxM136490Toyota Hilux SR DC 4X4 Auto Diesel CU76AJ TMM136490-WEEKLY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
39Maximo JH015 usage report March and April 22.xlsxM136635LEASED - Toyota Camry ASV70 CU87AJM136635-WEEKLY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
40Maximo JH015 usage report March and April 22.xlsxM136791Toyota Hilux 4x4 SR 2.8L T/Diesel Auto Double Cab - 1QG6BMM136791-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
41Maximo JH015 usage report March and April 22.xlsxM204009LEASED - Toyota Hilux SR5 DC 4x4 CY75TDM204009-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
42Maximo JH015 usage report March and April 22.xlsxM206983LEASED - Toyota Hilux DC 4x4 DD82LF TMM206983-DAILY29-Mar-22202203000001186072117John Holland Infrastructure25/02/202229/03/2022
43Maximo JH015 usage report March and April 22.xlsxM206983LEASED - Toyota Hilux DC 4x4 DD82LF TMM206983-MONTHLY-HIRE27-Apr-22202204000001186072117John Holland Infrastructure30/03/202227/04/2022
44Maximo JH015 usage report March and April 22.xlsxM130331Toyota - HiLux SR Ute - Dual Cab 4x4M130331-MONTHLY-HIRE27-Apr-22202204000001226508449PLANT BILLING - 5100 - SEAFORD PLANT DEPOT30/03/202227/04/2022
45Maximo JH015 usage report March and April 22.xlsxM130332Toyota - HiLux SR Ute - Dual Cab 4x4M130332-MONTHLY-HIRE27-Apr-22202204000001226508449PLANT BILLING - 5100 - SEAFORD PLANT DEPOT30/03/202227/04/2022
Maximo usage


The Billable mini sheet:

Shell charges April 2022 usage for May 22 Billing All.xlsx
ABCDEFGHI
1IndexTransaction Date/TimeHire_StartHire_EndCard NumberReg NumberDriverCost CentreRego_and_project.Asset
2101/04/2022703430 1084422713BL55VUUTE - POOL241160241160
3206/04/2022703430 1084422713BL55VUUTE - POOL241160241160
4307/04/2022703430 1084422713BL55VUUTE - POOL241160241160
5412/04/2022703430 1084422713BL55VUUTE - POOL241160241160
6514/04/2022703430 1084422713BL55VUUTE - POOL241160241160
7629/04/2022703430 1084422713BL55VUUTE - POOL241160241160
8729/04/2022703430 1096442873AQ97BVTRUCK - POOL325092325092
9804/04/2022703430 1098988659XN93ORTRUCK - POOL326329326329
10919/04/2022703430 1098988659XN93ORTRUCK - POOL326329326329
111001/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
121101/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
131202/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
141302/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
151404/04/2022703430 10926491091JR8FGTONY WINTON5100PM130331
161513/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
171613/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
181713/04/2022703430 10926491091JR8FGTONY WINTON5100PM130331
191815/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
201918/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
212018/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
222119/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
232219/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
242325/04/2022703430 10926490911JR8FFROBERT ROBINSON5100PM130332
Billable yes or no
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
510
Peter_SSs
Peter_SSs
A
  • Question Question
Vlookup formula to return specific text if cell contains match to list
Replies
3
Views
371
awoohaw
awoohaw
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
292
chris_bosten
C
C
  • Solved
Return a value in table based on the date within a range from a second table
Replies
5
Views
430
Peter_SSs
Peter_SSs
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
343
kbishop94
K

Forum statistics

Threads
1,223,231
Messages
6,170,884
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