Multiple Layer VLOOKUP, or better alternative?

[JukeExcel]

Hello All!

Trying to solve the following:

A purchase order is placed for a product on DATE A. (For purposes of demonstration, let's say July 1, 2022)

The product has a Part Number (which can be numeric or text) within a contract number (text) within a supplier number (text)

Prices for each Part Number can change over time depending on start and end date within a contract number and supplier number. Part Numbers can reside on multiple contracts at the same time with different suppliers.

Example:

PN123 Price within Contract ABC for Supplier XYX is:

$10 from 1/1/2020 to 12/31/2020
$12 from 1/1/2021 to 12/31/2021
$14 from 1/1/2022 to 12/31/2022
$16 from 1/1/2023 to 12/31/2023


Obviously in this simple example, DATE A in 2022 (so the price is $14)

I have many Part Numbers, Order Dates, Contracts and Suppliers so I need to look up the date range in which each order will fall so I can then look up the correct price at date.

In order to create a Lookup_Value, I have tried to concatenate SupplierNumber&ContractNumber&PartNumber but because I want the date to fall within a range of start and end dates, I can't also concatenate the PurchaseOrderDate.

I have tried concatenating SupplierNumber&ContractNumber&PartNumber&ContractStartDate in Column A of a new lookup Table_Array which The ContractRate in Column B, then setting my Lookup_value to SupplierNumber&ContractNumber&PartNumber&PurchaseOrderDate with a Range_lookup as 1 in order to catch the last date, but I can't get Excel to understand that.

What I think I need is either some way to name each array (maybe with VBA) or something that first defines the SupplierNumber section, then defines the ContractNumber within the Supplier, then defines the PartNumber within the Contract. Once I had the array defined at the correct Part Number, I could use Range_lookup of 1 to return the correct contract rate for the range of dates that my purchase order falls within.

Maybe Index Match is a better approach than VLookup. Not sure.

I've attached a sample spreadsheet. As you can I see, I also want to do the same with contract price at delivery date, but that's exactly the same mechanism as purchase order date.

 

[Cubist]

It's doable with formula. We can not do much with an image and must recreate the data for testing. Please post a mini sheet of your data in a copyable format.

 

[JukeExcel]

Supplier	Contract	Part Number	Start Date	End Date	Rate
A3456	LTC123456	ABCDEFG	12/1/10​	6/14/11​	96.36​
A3456	LTC123456	ABCDEFG	6/15/11​	6/14/12​	95.4​
A3456	LTC123456	ABCDEFG	6/15/12​	6/14/13​	94.44​
A3456	LTC123456	ABCDEFG	6/15/13​	6/14/14​	93.97​
A3456	LTC123456	ABCDEFG	6/15/14​	6/14/15​	93.5​
A3456	LTC123456	ABCDEFG	6/15/15​	6/14/16​	93.27​
A3456	LTC123456	ABCDEFG	6/15/16​	6/15/24​	93.03​
A3456	LTC123456	ABCDEFG	6/16/24​	6/15/26​	95.03​
A3456	LTC123456	HIJKLMNO-1	6/15/10​	6/14/11​	152.51​
A3456	LTC123456	HIJKLMNO-1	6/15/11​	6/14/12​	150.98​
A3456	LTC123456	HIJKLMNO-1	6/15/12​	6/14/13​	149.47​
A3456	LTC123456	HIJKLMNO-1	6/15/13​	6/14/14​	148.73​
A3456	LTC123456	HIJKLMNO-1	6/15/14​	6/14/15​	147.99​
A3456	LTC123456	HIJKLMNO-1	6/15/15​	6/14/16​	147.62​
A3456	LTC123456	HIJKLMNO-1	6/15/16​	6/15/24​	149.62​
A3456	LTC123456	HIJKLMNO-1	6/16/24​	6/15/26​	151.62​
A3456	LTC123456	HIJKLMNO-2	11/20/12​	6/14/13​	240.81​
A3456	LTC123456	HIJKLMNO-2	6/15/13​	6/15/24​	239.61​
A3456	LTC123456	HIJKLMNO-2	6/16/24​	6/15/26​	241.61​
A3456	LTC234567	HIJKLMNO-3	6/15/10​	6/14/11​	654.75​
A3456	LTC234567	HIJKLMNO-3	6/15/11​	6/14/12​	648.2​
A3456	LTC234567	HIJKLMNO-3	6/15/12​	6/14/13​	641.72​
A3456	LTC234567	HIJKLMNO-3	6/15/13​	6/14/14​	638.51​
A3456	LTC234567	HIJKLMNO-3	6/15/14​	6/14/15​	635.32​
A3456	LTC234567	HIJKLMNO-3	6/15/15​	6/14/16​	633.73​
A3456	LTC234567	HIJKLMNO-3	6/15/16​	6/15/24​	632.14​
A3456	LTC234567	HIJKLMNO-3	6/16/24​	6/15/26​	634.14​
A3456	LTC234567	696474-A	6/15/10​	6/14/11​	2.7​
A3456	LTC234567	696474-A	6/15/11​	6/14/12​	2.67​
A3456	LTC234567	696474-A	6/15/12​	6/14/13​	2.65​
A3456	LTC234567	696474-A	6/15/13​	6/14/14​	2.63​
A3456	LTC234567	696474-A	6/15/14​	6/14/15​	2.62​
A3456	LTC234567	696474-A	6/15/15​	6/14/16​	2.61​
A3456	LTC234567	696474-A	6/15/16​	6/15/24​	2.6​
A3456	LTC234567	696474-A	6/16/24​	6/15/26​	8.5​
A3456	LTC234567	61743-A34	6/15/10​	6/14/11​	3.34​
A3456	LTC234567	61743-A34	6/15/11​	6/14/12​	3.31​
A3456	LTC234567	61743-A34	6/15/12​	6/14/13​	3.27​
A3456	LTC234567	61743-A34	6/15/13​	6/14/14​	3.26​
A3456	LTC234567	61743-A34	6/15/14​	6/14/15​	3.24​
A3456	LTC234567	61743-A34	6/15/15​	6/14/16​	5.24​
A3456	LTC234567	61743-A34	6/15/16​	6/15/24​	7.24​
A3456	LTC234567	61743-A34	6/16/24​	6/15/26​	9.24​
C5678	LTC345678	901485-349K3Z	6/15/10​	6/14/11​	1.88​
C5678	LTC345678	901485-349K3Z	6/15/11​	6/14/12​	1.86​
C5678	LTC345678	901485-349K3Z	6/15/12​	6/14/13​	1.84​
C5678	LTC345678	901485-349K3Z	12/1/12​	6/15/24​	1.81​
C5678	LTC345678	901485-349K3Z	6/15/13​	6/14/14​	1.83​
C5678	LTC345678	901485-349K3Z	6/15/14​	6/14/15​	1.82​
C5678	LTC345678	901485-349K3Z	6/15/15​	6/14/16​	3.82​
C5678	LTC345678	901485-349K3Z	6/15/16​	6/15/24​	5.82​
C5678	LTC345678	901485-349K3Z	6/16/24​	6/15/26​	7.82​
C5678	LTC345678	1308278​	6/15/16​	6/15/24​	165.65​
C5678	LTC345678	1308278​	6/16/24​	6/15/26​	163.65​
C5678	LTC345678	198796-18AH	6/15/10​	6/14/11​	11.86​
C5678	LTC345678	198796-18AH	6/15/11​	6/14/12​	9.96​
C5678	LTC345678	198796-18AH	6/15/12​	6/14/13​	9.66​
C5678	LTC345678	198796-18AH	6/15/13​	6/14/14​	9.47​
C5678	LTC345678	198796-18AH	6/15/14​	6/14/15​	9.37​
C5678	LTC345678	198796-18AH	1/1/23​	6/15/24​	11.37​
C5678	LTC345678	198796-18AH	6/16/24​	6/15/26​	13.37​
C5678	LTC456789	976844​	6/15/16​	6/15/24​	2.28​
C5678	LTC456789	976844​	6/16/24​	6/15/26​	3.28​
C5678	LTC456789	396854​	6/15/16​	6/15/24​	17.74​
C5678	LTC456789	396854​	6/16/24​	6/15/26​	18.74​
C5678	LTC456789	1268058​	6/15/10​	6/14/11​	10.03​
C5678	LTC456789	1268058​	6/15/11​	6/14/12​	8.42​
C5678	LTC456789	1268058​	6/15/12​	6/14/13​	8.17​
C5678	LTC456789	1268058​	6/15/13​	6/14/14​	8.01​
C5678	LTC456789	1268058​	6/15/14​	6/14/15​	7.93​

 

[JukeExcel]

This is a table of the contract data including supplier number, contract number, part number, the date range and rate

 

[JukeExcel]

This is a table of the purchase order dates. I am trying to identify what the purchase price should be by looking up the correct rate based on the purchase date according to the correct date range in the table above

Supplier	Contract	Part Number	Purchase Order Create Date	Rate at PO Create
A3456	LTC123456	ABCDEFG	3/28/23​
A3456	LTC123456	HIJKLMNO-1	8/8/12​
A3456	LTC123456	HIJKLMNO-2	11/28/13​
A3456	LTC234567	HIJKLMNO-3	6/24/14​
A3456	LTC234567	696474-A	7/2/12​
A3456	LTC234567	61743-A34	3/6/18​
C5678	LTC345678	901485-349K3Z	7/3/14​
C5678	LTC345678	1308278	12/8/22​
C5678	LTC345678	198796-18AH	7/19/15​
C5678	LTC456789	976844	12/6/14​
C5678	LTC456789	396854	3/10/11​
C5678	LTC456789	1268058	8/15/14​
A3456	LTC123456	ABCDEFG	2/9/12​
A3456	LTC123456	HIJKLMNO-1	6/30/16​
A3456	LTC123456	HIJKLMNO-2	5/10/16​
A3456	LTC234567	HIJKLMNO-3	2/25/23​
A3456	LTC234567	696474-A	12/14/18​
A3456	LTC234567	61743-A34	10/11/14​
C5678	LTC345678	901485-349K3Z	9/23/22​
C5678	LTC345678	1308278	6/29/16​
C5678	LTC345678	198796-18AH	5/31/15​
C5678	LTC456789	976844	9/26/13​
C5678	LTC456789	396854	12/9/17​
C5678	LTC456789	1268058	11/23/16​

 

[Cubist]

Try this. There are some overlapping periods (highlighted in yellow) so it returned multiple results.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Supplier	Contract	Part Number	Start Date	End Date	Rates		Supplier	Contract	Part Number	Purchase Order Create Date	Rate at PO Create
2	A3456	LTC123456	ABCDEFG	12/1/10	6/14/11	96.36		A3456	LTC123456	ABCDEFG	3/28/23	93.03
3	A3456	LTC123456	ABCDEFG	6/15/11	6/14/12	95.40		A3456	LTC123456	HIJKLMNO-1	8/8/12	149.47
4	A3456	LTC123456	ABCDEFG	6/15/12	6/14/13	94.44		A3456	LTC123456	HIJKLMNO-2	11/28/13	239.61
5	A3456	LTC123456	ABCDEFG	6/15/13	6/14/14	93.97		A3456	LTC234567	HIJKLMNO-3	6/24/14	635.32
6	A3456	LTC123456	ABCDEFG	6/15/14	6/14/15	93.50		A3456	LTC234567	696474-A	7/2/12	2.65
7	A3456	LTC123456	ABCDEFG	6/15/15	6/14/16	93.27		A3456	LTC234567	61743-A34	3/6/18	7.24
8	A3456	LTC123456	ABCDEFG	6/15/16	6/15/24	93.03		C5678	LTC345678	901485-349K3Z	7/3/14	1.81	1.82
9	A3456	LTC123456	ABCDEFG	6/16/24	6/15/26	95.03		C5678	LTC345678	1308278	12/8/22	165.65
10	A3456	LTC123456	HIJKLMNO-1	6/15/10	6/14/11	152.51		C5678	LTC345678	198796-18AH	7/19/15	not found
11	A3456	LTC123456	HIJKLMNO-1	6/15/11	6/14/12	150.98		C5678	LTC456789	976844	12/6/14	not found
12	A3456	LTC123456	HIJKLMNO-1	6/15/12	6/14/13	149.47		C5678	LTC456789	396854	3/10/11	not found
13	A3456	LTC123456	HIJKLMNO-1	6/15/13	6/14/14	148.73		C5678	LTC456789	1268058	8/15/14	7.93
14	A3456	LTC123456	HIJKLMNO-1	6/15/14	6/14/15	147.99		A3456	LTC123456	ABCDEFG	2/9/12	95.4
15	A3456	LTC123456	HIJKLMNO-1	6/15/15	6/14/16	147.62		A3456	LTC123456	HIJKLMNO-1	6/30/16	149.62
16	A3456	LTC123456	HIJKLMNO-1	6/15/16	6/15/24	149.62		A3456	LTC123456	HIJKLMNO-2	5/10/16	239.61
17	A3456	LTC123456	HIJKLMNO-1	6/16/24	6/15/26	151.62		A3456	LTC234567	HIJKLMNO-3	2/25/23	632.14
18	A3456	LTC123456	HIJKLMNO-2	11/20/12	6/14/13	240.81		A3456	LTC234567	696474-A	12/14/18	2.6
19	A3456	LTC123456	HIJKLMNO-2	6/15/13	6/15/24	239.61		A3456	LTC234567	61743-A34	10/11/14	3.24
20	A3456	LTC123456	HIJKLMNO-2	6/16/24	6/15/26	241.61		C5678	LTC345678	901485-349K3Z	9/23/22	1.81	5.82
21	A3456	LTC234567	HIJKLMNO-3	6/15/10	6/14/11	654.75		C5678	LTC345678	1308278	6/29/16	165.65
22	A3456	LTC234567	HIJKLMNO-3	6/15/11	6/14/12	648.20		C5678	LTC345678	198796-18AH	5/31/15	9.37
23	A3456	LTC234567	HIJKLMNO-3	6/15/12	6/14/13	641.72		C5678	LTC456789	976844	9/26/13	not found
24	A3456	LTC234567	HIJKLMNO-3	6/15/13	6/14/14	638.51		C5678	LTC456789	396854	12/9/17	17.74
25	A3456	LTC234567	HIJKLMNO-3	6/15/14	6/14/15	635.32		C5678	LTC456789	1268058	11/23/16	not found
26	A3456	LTC234567	HIJKLMNO-3	6/15/15	6/14/16	633.73
27	A3456	LTC234567	HIJKLMNO-3	6/15/16	6/15/24	632.14
28	A3456	LTC234567	HIJKLMNO-3	6/16/24	6/15/26	634.14
29	A3456	LTC234567	696474-A	6/15/10	6/14/11	2.70
30	A3456	LTC234567	696474-A	6/15/11	6/14/12	2.67
31	A3456	LTC234567	696474-A	6/15/12	6/14/13	2.65
32	A3456	LTC234567	696474-A	6/15/13	6/14/14	2.63
33	A3456	LTC234567	696474-A	6/15/14	6/14/15	2.62
34	A3456	LTC234567	696474-A	6/15/15	6/14/16	2.61
35	A3456	LTC234567	696474-A	6/15/16	6/15/24	2.60
36	A3456	LTC234567	696474-A	6/16/24	6/15/26	8.50
37	A3456	LTC234567	61743-A34	6/15/10	6/14/11	3.34
38	A3456	LTC234567	61743-A34	6/15/11	6/14/12	3.31
39	A3456	LTC234567	61743-A34	6/15/12	6/14/13	3.27
40	A3456	LTC234567	61743-A34	6/15/13	6/14/14	3.26
41	A3456	LTC234567	61743-A34	6/15/14	6/14/15	3.24
42	A3456	LTC234567	61743-A34	6/15/15	6/14/16	5.24
43	A3456	LTC234567	61743-A34	6/15/16	6/15/24	7.24
44	A3456	LTC234567	61743-A34	6/16/24	6/15/26	9.24
45	C5678	LTC345678	901485-349K3Z	6/15/10	6/14/11	1.88
46	C5678	LTC345678	901485-349K3Z	6/15/11	6/14/12	1.86
47	C5678	LTC345678	901485-349K3Z	6/15/12	6/14/13	1.84
48	C5678	LTC345678	901485-349K3Z	12/1/12	6/15/24	1.81
49	C5678	LTC345678	901485-349K3Z	6/15/13	6/14/14	1.83
50	C5678	LTC345678	901485-349K3Z	6/15/14	6/14/15	1.82
51	C5678	LTC345678	901485-349K3Z	6/15/15	6/14/16	3.82
52	C5678	LTC345678	901485-349K3Z	6/15/16	6/15/24	5.82
53	C5678	LTC345678	901485-349K3Z	6/16/24	6/15/26	7.82
54	C5678	LTC345678	1308278	6/15/16	6/15/24	165.65
55	C5678	LTC345678	1308278	6/16/24	6/15/26	163.65
56	C5678	LTC345678	198796-18AH	6/15/10	6/14/11	11.86
57	C5678	LTC345678	198796-18AH	6/15/11	6/14/12	9.96
58	C5678	LTC345678	198796-18AH	6/15/12	6/14/13	9.66
59	C5678	LTC345678	198796-18AH	6/15/13	6/14/14	9.47
60	C5678	LTC345678	198796-18AH	6/15/14	6/14/15	9.37
61	C5678	LTC345678	198796-18AH	1/1/23	6/15/24	11.37
62	C5678	LTC345678	198796-18AH	6/16/24	6/15/26	13.37
63	C5678	LTC456789	976844	6/15/16	6/15/24	2.28
64	C5678	LTC456789	976844	6/16/24	6/15/26	3.28
65	C5678	LTC456789	396854	6/15/16	6/15/24	17.74
66	C5678	LTC456789	396854	6/16/24	6/15/26	18.74
67	C5678	LTC456789	1268058	6/15/10	6/14/11	10.03
68	C5678	LTC456789	1268058	6/15/11	6/14/12	8.42
69	C5678	LTC456789	1268058	6/15/12	6/14/13	8.17
70	C5678	LTC456789	1268058	6/15/13	6/14/14	8.01
71	C5678	LTC456789	1268058	6/15/14	6/14/15	7.93
Sheet2

Cell Formulas
Range	Formula
L2:L7,L9:L19,L21:L25,L20:M20,L8:M8	L2	=TOROW(FILTER($F$2:$F$71,($A$2:$A$71=H2)*($B$2:$B$71=I2)*($C$2:$C$71=VALUETOTEXT(J2))*($D$2:$D$71<=K2)*($E$2:$E$71>=K2),"not found"))

 

[JukeExcel]

This is terrific - thank you so much!

I notice that in the solution you provided above, sometimes Part Numbers that are pure numbers (not strings) return a rate, and sometimes it returns "not found."

I also plugged in the formula to a larger dataset I have, and it returns "not found" for all instances where the part is a number. Any insights?

Supplier	Contract	Part Number	Start Date	End Date	Rate		Supplier	Contract	Part Number	Purchase Order Create Date	Rate at PO Create
C5678	LTC456789	1268058	6/15/2010​	6/14/2011​	10.03​		C5678	LTC456789	1268058​	4/29/2011​	not found
C5678	LTC456789	1268058	6/15/2011​	6/14/2012​	8.42​		C5678	LTC456789	1268058​	4/20/2012​	not found
C5678	LTC456789	1268058	6/15/2012​	6/14/2013​	8.17​		C5678	LTC456789	1268058​	10/2/2014​	not found
C5678	LTC456789	1268058	6/15/2013​	6/14/2014​	8.01​		C5678	LTC456789	1268058​	5/22/2012​	not found

 

[JukeExcel]

This is terrific - thank you so much!

I notice that in the solution you provided above, sometimes Part Numbers that are pure numbers (not strings) return a rate, and sometimes it returns "not found."

I also plugged in the formula to a larger dataset I have, and it returns "not found" for all instances where the part is a number. Any insights?

Supplier	Contract	Part Number	Start Date	End Date	Rate		Supplier	Contract	Part Number	Purchase Order Create Date	Rate at PO Create
C5678	LTC456789	1268058	6/15/2010​	6/14/2011​	10.03​		C5678	LTC456789	1268058​	4/29/2011​	not found
C5678	LTC456789	1268058	6/15/2011​	6/14/2012​	8.42​		C5678	LTC456789	1268058​	4/20/2012​	not found
C5678	LTC456789	1268058	6/15/2012​	6/14/2013​	8.17​		C5678	LTC456789	1268058​	10/2/2014​	not found
C5678	LTC456789	1268058	6/15/2013​	6/14/2014​	8.01​		C5678	LTC456789	1268058​	5/22/2012​	not found

It seems there is some issue in the VALUETOTEXT portion of the formula. I tried removing that and it still did not seem to work

 

[Cubist]

What happens if you add this bit.

=TOROW(FILTER($F$2:$F$71,($A$2:$A$71=H2)*($B$2:$B$71=I2)*(VALUETOTEXT($C$2:$C$71)=VALUETOTEXT(J2))*($D$2:$D$71<=K2)*($E$2:$E$71>=K2),"not found"))

 

[JukeExcel]

That worked! Thank you so much!