Hi All,
I'll do my best to explain my issue. It's a similar problem to threads that I have seen resolved, but none seem to fit my problem... (I've included some context at the bottom of the post)
Essentially, I need to perform a 3 (or 2) way lookup and return a result from a field on the data table that I specify (could be text or numerical):
Criteria 1: A criteria from my entry sheet (Registration #) on a data table that will have many instances of this criteria
Combined with (and)
Criteria 2 & 3 (I assume 1 for >date and one for < date): Needs to identify if the single date value on my entry sheet (Penalty Date) falls between a start and end date in my data table (2 columns)
To provide some context here... I have a list of hundreds of vehicles that I have hired to thousands of customers over a range of dates. This data sits in a large table.
What I am hoping to acheive is to have another sheet where I can key in a Registration Number and a Penalty Date and have a formula return the customer name (from the Data Table) who had the vehicle in their possession at the time where the penalty was incurred. We have thousands of toll notices and penalty notices hence why I wish to tackle in this format rather than just using filters...
Given that the penalty date will often not fall on the start or end date, but somewhere in between, I don't think that I can use the INDEX MATCH type solution that I have seen around using the concatenation step.
I hope this makes some sense.
Any help is appreciated!
Thanks
I'll do my best to explain my issue. It's a similar problem to threads that I have seen resolved, but none seem to fit my problem... (I've included some context at the bottom of the post)
Essentially, I need to perform a 3 (or 2) way lookup and return a result from a field on the data table that I specify (could be text or numerical):
Criteria 1: A criteria from my entry sheet (Registration #) on a data table that will have many instances of this criteria
Combined with (and)
Criteria 2 & 3 (I assume 1 for >date and one for < date): Needs to identify if the single date value on my entry sheet (Penalty Date) falls between a start and end date in my data table (2 columns)
To provide some context here... I have a list of hundreds of vehicles that I have hired to thousands of customers over a range of dates. This data sits in a large table.
What I am hoping to acheive is to have another sheet where I can key in a Registration Number and a Penalty Date and have a formula return the customer name (from the Data Table) who had the vehicle in their possession at the time where the penalty was incurred. We have thousands of toll notices and penalty notices hence why I wish to tackle in this format rather than just using filters...
Given that the penalty date will often not fall on the start or end date, but somewhere in between, I don't think that I can use the INDEX MATCH type solution that I have seen around using the concatenation step.
I hope this makes some sense.
Any help is appreciated!
Thanks
