Returning a text value from a multi criteria lookup using an exact match and between statement

[Dylancam]

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

 

[Aladin Akyurek]

Try to post a scaled-down sample along with the desired result. For providing a sample, Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

 

[Dylancam]

Try to post a scaled-down sample along with the desired result. For providing a sample, Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit (Note that an image or a link to a storage site like dropbox or onedrive etc. does not provide a permanent record.). Mind you a forum without a permanent record is not of much value.

Data Table and Entry Table example below. I'd like to enter the first 2 columns on the entry table and have the remaining values (highlighted in blue) to populate from a formula

DATA TABLE
Excel 2010 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	Rego​	Hire_Ref​	Branch_Code​	Hire_No​	Line_No​	Cust_Code​	CustName​	fleet_no​	fld_Desc​	Line_StDate​	Term_Date​	Return_Date​
2​	QQ25II	5878555_1	ROCL	5878555​	1​	COMM6003	Commercial & Industrial Property Pty Ltd	1103934​	Trailer - Machine (Roller)	21/04/2016​	22/04/2016​	22/04/2016​
3​	QQ25II	5716135_3	GEEB	5716135​	3​	BMDU7000	BMD Urban Pty Ltd	1103934​	Trailer - Machine (Roller)	12/01/2016​	2/03/2016​	2/03/2016​
4​	QQ25II	5675204_3	GEEB	5675204​	3​	BMDC9528	BMD CONSTRUCTIONS PTY LTD	1103934​	Trailer - Machine (Roller)	14/12/2015​	11/01/2016​	12/01/2016​
5​	QQ25II	5672710_3	STRP	5672710​	3​	CASH-STRP	All Stars Asphalt	1103934​	Trailer - Machine (Roller)	10/12/2015​	10/12/2015​	10/12/2015​
6​	QQ25II	5667701_3	STRP	5667701​	3​	CASH-STRP	All Stars Asphalt	1103934​	Trailer - Machine (Roller)	8/12/2015​	8/12/2015​	8/12/2015​
7​	000QZO	5397211_4	BILO	5397211​	4​	angl6004	ANGLO COAL (CALLIDE MANAGEMENT) PTY LTD	1150125​	Lighting Tower LED 130-165 Lumens Mine Spec	1/07/2015​	NULL	NULL
8​	AA63BB	5938918_3	CRNS	5938918​	3​	CASH-CRNS	Issac Marshall	1103935​	Trailer - Machine (Roller)	31/05/2016​	NULL	NULL
9​	AA63BB	5903141_2	CRNS	5903141​	2​	ROAD6005	ROADTEK ASSET SERVICES	1103935​	Trailer - Machine (Roller)	9/05/2016​	20/05/2016​	20/05/2016​
10​	AA63BB	5900525_2	CRNS	5900525​	2​	RICH6026	RICHARDSONS ELECTRICAL SERVICE	1103935​	Trailer - Machine (Roller)	6/05/2016​	6/05/2016​	6/05/2016​
11​	AA63BB	5803644_2	CRNS	5803644​	2​	CASH-CRNS	Patrick McCarthy	1103935​	Trailer - Machine (Roller)	11/03/2016​	18/03/2016​	18/03/2016​
12​	AA63BB	5745954_3	CRNS	5745954​	3​	CASH-CRNS	Patrick McCarthy	1103935​	Trailer - Machine (Roller)	4/02/2016​	18/02/2016​	18/02/2016​
13​	AA63BB	5710966_3	CRNS	5710966​	3​	CASH-CRNS	Patrick McCarthy	1103935​	Trailer - Machine (Roller)	8/01/2016​	15/01/2016​	15/02/2016​
14​	AA63BB	5652350_3	CRNS	5652350​	3​	CASH-CRNS	Patrick McCarthy	1103935​	Trailer - Machine (Roller)	3/12/2015​	7/12/2015​	7/12/2015​
15​	001QZO	5397211_2	BILO	5397211​	2​	angl6004	ANGLO COAL (CALLIDE MANAGEMENT) PTY LTD	1150126​	Lighting Tower LED 130-165 Lumens Mine Spec	1/07/2015​	NULL	NULL
16​	001SOZ	5771884_1	MUDG	5771884​	1​	OSTW9500	OSTWALD BROS CIVIL PTY LTD	1093335​	Truck - Tipper 10-12 Ton 6X4	16/02/2016​	NULL	NULL
17​	001SOZ	5629835_1	MUDG	5629835​	1​	ULAN4733	ULAN WATER PTY LTD	1093335​	Truck - Tipper 10-12 Ton 6X4	23/11/2015​	4/02/2016​	10/02/2016​
18​	003QZW	5868687_3	MRBH	5868687​	3​	GOLD4075	GOLDING CONTRACTORS PTY LTD	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	15/04/2016​	NULL	NULL
19​	003QZW	5801674_33	CRNS	5801674​	33​	GOOD8476	GOODLINE	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	11/03/2016​	1/04/2016​	1/04/2016​
20​	003QZW	5755254_2	BLKW	5755254​	2​	UNIT6005	UNITED GROUP RESOURCES SERVICES LTD	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	5/02/2016​	12/02/2016​	18/02/2016​
21​	003QZW	5741708_10	BLKW	5741708​	10​	UNIT6005	UNITED GROUP RESOURCES SERVICES LTD	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	3/02/2016​	5/02/2016​	5/02/2016​
22​	003QZW	5731278_1	BLKW	5731278​	1​	qrna4000	Aurizon Operations Limited	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	20/01/2016​	24/01/2016​	28/01/2016​
23​	003QZW	5711161_4	BLKW	5711161​	4​	qrna4000	Aurizon Operations Limited	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	11/01/2016​	11/01/2016​	13/01/2016​
24​	003QZW	5680862_1	BLKW	5680862​	1​	qrna4000	Aurizon Operations Limited	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	15/12/2015​	17/12/2015​	18/12/2015​
25​	003QZW	5664090_5	BLKW	5664090​	5​	UNIT6005	UNITED GROUP RESOURCES SERVICES LTD	1150146​	Lighting Tower LED 130-165 Lumens Mine Spec	8/12/2015​	11/12/2015​	11/12/2015​
26​	003QZW	5397211_14	BILO	5397211​	14​	angl6004	ANGLO COAL (CALLIDE MANAGEMENT) PTY LTD	1150145​	Lighting Tower LED 130-165 Lumens Mine Spec	1/09/2015​	NULL	NULL
27​	003QZW	5900167_8	GLST	5900167​	8​	ROAD6005	ROADTEK ASSET SERVICES	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	17/05/2016​	NULL	NULL
28​	003QZW	5890081_1	EMLD	5890081​	1​	wesf0453	WESFARMERS CURRAGH PTY LTD	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	20/04/2016​	4/05/2016​	6/05/2016​
29​	003QZW	5872266_4	EMLD	5872266​	4​	qrna4000	Aurizon Operations Limited	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	15/04/2016​	15/04/2016​	20/04/2016​
30​	003QZW	5862469_6	EMLD	5862469​	6​	qrna4000	Aurizon Operations Limited	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	10/04/2016​	12/04/2016​	12/04/2016​
31​	003QZW	5842459_8	EMLD	5842459​	8​	TRAC9867	Rhomberg Rail Australia Pty Ltd	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	2/04/2016​	4/04/2016​	6/04/2016​
32​	003QZW	5807810_7	EMLD	5807810​	7​	qrna4000	Aurizon Operations Limited	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	20/03/2016​	21/03/2016​	24/03/2016​
33​	003QZW	5775904_1	BLKW	5775904​	1​	TJWH9580	T & J WHITEHOUSE	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	17/02/2016​	29/02/2016​	16/03/2016​
34​	003QZW	5118890_15	BLKW	5118890​	15​	CQMI1545	WOLFF MINING PTY LTD	1150144​	Lighting Tower LED 130-165 Lumens Mine Spec	20/07/2015​	9/02/2016​	16/02/2016​

Sheet: All hire

Entry Table

Excel 2010 32 bit

	B​	C​	D​	E​	F​	G​	H​
5​	Rego #​	Date​	Branch_Code​	CustName​	Hire_No​	fleet_no​	fld_Desc​
6​	QQ25II	17/12/2015​
7​	001SOZ	18/02/2016​
8​	003QZW	23/01/2016​

Sheet: Sheet1

 

[Aladin Akyurek]

The date in C6 of Sheet1 must be between Line_StDate and Term_Date, right?

 

[Dylancam]

The date in C6 of Sheet1 must be between Line_StDate and Term_Date, right?

Aladin,

Equal to or between Line_StDate and Return_Date.

Thanks for your help

 

[Aladin Akyurek]

Aladin,

Equal to or between Line_StDate and Return_Date.

Thanks for your help

Row\Col	A​	B​	C​	D​	E​	F​	G​
5​	Rego #	Date	Branch_Code	CustName	Hire_No	fleet_no	fld_Desc
6​	QQ25II	12/17/2015	GEEB	BMD CONSTRUCTIONS PTY LTD	5675204	1103934	Trailer - Machine (Roller)
7​	001SOZ	2/18/2016	MUDG	OSTWALD BROS CIVIL PTY LTD	5771884	1093335	Truck - Tipper 10-12 Ton 6X4
8​	003QZW	1/23/2016	BLKW	Aurizon Operations Limited	5731278	1150146	Lighting Tower LED 130-165 Lumens Mine Spec

In C6 of Sheet1 control+shift+enter, not just enter, copy across, and down:

=INDEX('All Hire'!$A$2:$M$34,MATCH($A6,IF($B6>='All Hire'!$J$2:$J$34,
    IF($B6<='All Hire'!$K$2:$K$34,'All Hire'!$A$2:$A$34)),0),
    MATCH(C$5,'All Hire'!$A$1:$L$1,0))

 

[Dylancam]

Worked perfectly! You're a ledgend! I need to wrap my head around Index and Match functions a bit better.

Thanks for your help!

 

[Aladin Akyurek]

Worked perfectly! You're a ledgend! I need to wrap my head around Index and Match functions a bit better.

Thanks for your help!

You are welcome.