Multiple Layer VLOOKUP, or better alternative?

J

JukeExcel

New Member
Joined
Jun 19, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.

1718831700325.png

1718831746761.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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.
 
Upvote 0
SupplierContractPart NumberStart DateEnd DateRate
A3456LTC123456ABCDEFG
12/1/10​
6/14/11​
96.36​
A3456LTC123456ABCDEFG
6/15/11​
6/14/12​
95.4​
A3456LTC123456ABCDEFG
6/15/12​
6/14/13​
94.44​
A3456LTC123456ABCDEFG
6/15/13​
6/14/14​
93.97​
A3456LTC123456ABCDEFG
6/15/14​
6/14/15​
93.5​
A3456LTC123456ABCDEFG
6/15/15​
6/14/16​
93.27​
A3456LTC123456ABCDEFG
6/15/16​
6/15/24​
93.03​
A3456LTC123456ABCDEFG
6/16/24​
6/15/26​
95.03​
A3456LTC123456HIJKLMNO-1
6/15/10​
6/14/11​
152.51​
A3456LTC123456HIJKLMNO-1
6/15/11​
6/14/12​
150.98​
A3456LTC123456HIJKLMNO-1
6/15/12​
6/14/13​
149.47​
A3456LTC123456HIJKLMNO-1
6/15/13​
6/14/14​
148.73​
A3456LTC123456HIJKLMNO-1
6/15/14​
6/14/15​
147.99​
A3456LTC123456HIJKLMNO-1
6/15/15​
6/14/16​
147.62​
A3456LTC123456HIJKLMNO-1
6/15/16​
6/15/24​
149.62​
A3456LTC123456HIJKLMNO-1
6/16/24​
6/15/26​
151.62​
A3456LTC123456HIJKLMNO-2
11/20/12​
6/14/13​
240.81​
A3456LTC123456HIJKLMNO-2
6/15/13​
6/15/24​
239.61​
A3456LTC123456HIJKLMNO-2
6/16/24​
6/15/26​
241.61​
A3456LTC234567HIJKLMNO-3
6/15/10​
6/14/11​
654.75​
A3456LTC234567HIJKLMNO-3
6/15/11​
6/14/12​
648.2​
A3456LTC234567HIJKLMNO-3
6/15/12​
6/14/13​
641.72​
A3456LTC234567HIJKLMNO-3
6/15/13​
6/14/14​
638.51​
A3456LTC234567HIJKLMNO-3
6/15/14​
6/14/15​
635.32​
A3456LTC234567HIJKLMNO-3
6/15/15​
6/14/16​
633.73​
A3456LTC234567HIJKLMNO-3
6/15/16​
6/15/24​
632.14​
A3456LTC234567HIJKLMNO-3
6/16/24​
6/15/26​
634.14​
A3456LTC234567696474-A
6/15/10​
6/14/11​
2.7​
A3456LTC234567696474-A
6/15/11​
6/14/12​
2.67​
A3456LTC234567696474-A
6/15/12​
6/14/13​
2.65​
A3456LTC234567696474-A
6/15/13​
6/14/14​
2.63​
A3456LTC234567696474-A
6/15/14​
6/14/15​
2.62​
A3456LTC234567696474-A
6/15/15​
6/14/16​
2.61​
A3456LTC234567696474-A
6/15/16​
6/15/24​
2.6​
A3456LTC234567696474-A
6/16/24​
6/15/26​
8.5​
A3456LTC23456761743-A34
6/15/10​
6/14/11​
3.34​
A3456LTC23456761743-A34
6/15/11​
6/14/12​
3.31​
A3456LTC23456761743-A34
6/15/12​
6/14/13​
3.27​
A3456LTC23456761743-A34
6/15/13​
6/14/14​
3.26​
A3456LTC23456761743-A34
6/15/14​
6/14/15​
3.24​
A3456LTC23456761743-A34
6/15/15​
6/14/16​
5.24​
A3456LTC23456761743-A34
6/15/16​
6/15/24​
7.24​
A3456LTC23456761743-A34
6/16/24​
6/15/26​
9.24​
C5678LTC345678901485-349K3Z
6/15/10​
6/14/11​
1.88​
C5678LTC345678901485-349K3Z
6/15/11​
6/14/12​
1.86​
C5678LTC345678901485-349K3Z
6/15/12​
6/14/13​
1.84​
C5678LTC345678901485-349K3Z
12/1/12​
6/15/24​
1.81​
C5678LTC345678901485-349K3Z
6/15/13​
6/14/14​
1.83​
C5678LTC345678901485-349K3Z
6/15/14​
6/14/15​
1.82​
C5678LTC345678901485-349K3Z
6/15/15​
6/14/16​
3.82​
C5678LTC345678901485-349K3Z
6/15/16​
6/15/24​
5.82​
C5678LTC345678901485-349K3Z
6/16/24​
6/15/26​
7.82​
C5678LTC345678
1308278​
6/15/16​
6/15/24​
165.65​
C5678LTC345678
1308278​
6/16/24​
6/15/26​
163.65​
C5678LTC345678198796-18AH
6/15/10​
6/14/11​
11.86​
C5678LTC345678198796-18AH
6/15/11​
6/14/12​
9.96​
C5678LTC345678198796-18AH
6/15/12​
6/14/13​
9.66​
C5678LTC345678198796-18AH
6/15/13​
6/14/14​
9.47​
C5678LTC345678198796-18AH
6/15/14​
6/14/15​
9.37​
C5678LTC345678198796-18AH
1/1/23​
6/15/24​
11.37​
C5678LTC345678198796-18AH
6/16/24​
6/15/26​
13.37​
C5678LTC456789
976844​
6/15/16​
6/15/24​
2.28​
C5678LTC456789
976844​
6/16/24​
6/15/26​
3.28​
C5678LTC456789
396854​
6/15/16​
6/15/24​
17.74​
C5678LTC456789
396854​
6/16/24​
6/15/26​
18.74​
C5678LTC456789
1268058​
6/15/10​
6/14/11​
10.03​
C5678LTC456789
1268058​
6/15/11​
6/14/12​
8.42​
C5678LTC456789
1268058​
6/15/12​
6/14/13​
8.17​
C5678LTC456789
1268058​
6/15/13​
6/14/14​
8.01​
C5678LTC456789
1268058​
6/15/14​
6/14/15​
7.93​
 
Upvote 0
This is a table of the contract data including supplier number, contract number, part number, the date range and rate
 
Upvote 0
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

SupplierContractPart NumberPurchase Order Create DateRate at PO Create
A3456LTC123456ABCDEFG
3/28/23​
A3456LTC123456HIJKLMNO-1
8/8/12​
A3456LTC123456HIJKLMNO-2
11/28/13​
A3456LTC234567HIJKLMNO-3
6/24/14​
A3456LTC234567696474-A
7/2/12​
A3456LTC23456761743-A34
3/6/18​
C5678LTC345678901485-349K3Z
7/3/14​
C5678LTC3456781308278
12/8/22​
C5678LTC345678198796-18AH
7/19/15​
C5678LTC456789976844
12/6/14​
C5678LTC456789396854
3/10/11​
C5678LTC4567891268058
8/15/14​
A3456LTC123456ABCDEFG
2/9/12​
A3456LTC123456HIJKLMNO-1
6/30/16​
A3456LTC123456HIJKLMNO-2
5/10/16​
A3456LTC234567HIJKLMNO-3
2/25/23​
A3456LTC234567696474-A
12/14/18​
A3456LTC23456761743-A34
10/11/14​
C5678LTC345678901485-349K3Z
9/23/22​
C5678LTC3456781308278
6/29/16​
C5678LTC345678198796-18AH
5/31/15​
C5678LTC456789976844
9/26/13​
C5678LTC456789396854
12/9/17​
C5678LTC4567891268058
11/23/16​
 
Upvote 0
Try this. There are some overlapping periods (highlighted in yellow) so it returned multiple results.
Book1
ABCDEFGHIJKLM
1SupplierContractPart NumberStart DateEnd DateRatesSupplierContractPart NumberPurchase Order Create DateRate at PO Create
2A3456LTC123456ABCDEFG12/1/106/14/1196.36A3456LTC123456ABCDEFG3/28/2393.03
3A3456LTC123456ABCDEFG6/15/116/14/1295.40A3456LTC123456HIJKLMNO-18/8/12149.47
4A3456LTC123456ABCDEFG6/15/126/14/1394.44A3456LTC123456HIJKLMNO-211/28/13239.61
5A3456LTC123456ABCDEFG6/15/136/14/1493.97A3456LTC234567HIJKLMNO-36/24/14635.32
6A3456LTC123456ABCDEFG6/15/146/14/1593.50A3456LTC234567696474-A7/2/122.65
7A3456LTC123456ABCDEFG6/15/156/14/1693.27A3456LTC23456761743-A343/6/187.24
8A3456LTC123456ABCDEFG6/15/166/15/2493.03C5678LTC345678901485-349K3Z7/3/141.811.82
9A3456LTC123456ABCDEFG6/16/246/15/2695.03C5678LTC345678130827812/8/22165.65
10A3456LTC123456HIJKLMNO-16/15/106/14/11152.51C5678LTC345678198796-18AH7/19/15not found
11A3456LTC123456HIJKLMNO-16/15/116/14/12150.98C5678LTC45678997684412/6/14not found
12A3456LTC123456HIJKLMNO-16/15/126/14/13149.47C5678LTC4567893968543/10/11not found
13A3456LTC123456HIJKLMNO-16/15/136/14/14148.73C5678LTC45678912680588/15/147.93
14A3456LTC123456HIJKLMNO-16/15/146/14/15147.99A3456LTC123456ABCDEFG2/9/1295.4
15A3456LTC123456HIJKLMNO-16/15/156/14/16147.62A3456LTC123456HIJKLMNO-16/30/16149.62
16A3456LTC123456HIJKLMNO-16/15/166/15/24149.62A3456LTC123456HIJKLMNO-25/10/16239.61
17A3456LTC123456HIJKLMNO-16/16/246/15/26151.62A3456LTC234567HIJKLMNO-32/25/23632.14
18A3456LTC123456HIJKLMNO-211/20/126/14/13240.81A3456LTC234567696474-A12/14/182.6
19A3456LTC123456HIJKLMNO-26/15/136/15/24239.61A3456LTC23456761743-A3410/11/143.24
20A3456LTC123456HIJKLMNO-26/16/246/15/26241.61C5678LTC345678901485-349K3Z9/23/221.815.82
21A3456LTC234567HIJKLMNO-36/15/106/14/11654.75C5678LTC34567813082786/29/16165.65
22A3456LTC234567HIJKLMNO-36/15/116/14/12648.20C5678LTC345678198796-18AH5/31/159.37
23A3456LTC234567HIJKLMNO-36/15/126/14/13641.72C5678LTC4567899768449/26/13not found
24A3456LTC234567HIJKLMNO-36/15/136/14/14638.51C5678LTC45678939685412/9/1717.74
25A3456LTC234567HIJKLMNO-36/15/146/14/15635.32C5678LTC456789126805811/23/16not found
26A3456LTC234567HIJKLMNO-36/15/156/14/16633.73
27A3456LTC234567HIJKLMNO-36/15/166/15/24632.14
28A3456LTC234567HIJKLMNO-36/16/246/15/26634.14
29A3456LTC234567696474-A6/15/106/14/112.70
30A3456LTC234567696474-A6/15/116/14/122.67
31A3456LTC234567696474-A6/15/126/14/132.65
32A3456LTC234567696474-A6/15/136/14/142.63
33A3456LTC234567696474-A6/15/146/14/152.62
34A3456LTC234567696474-A6/15/156/14/162.61
35A3456LTC234567696474-A6/15/166/15/242.60
36A3456LTC234567696474-A6/16/246/15/268.50
37A3456LTC23456761743-A346/15/106/14/113.34
38A3456LTC23456761743-A346/15/116/14/123.31
39A3456LTC23456761743-A346/15/126/14/133.27
40A3456LTC23456761743-A346/15/136/14/143.26
41A3456LTC23456761743-A346/15/146/14/153.24
42A3456LTC23456761743-A346/15/156/14/165.24
43A3456LTC23456761743-A346/15/166/15/247.24
44A3456LTC23456761743-A346/16/246/15/269.24
45C5678LTC345678901485-349K3Z6/15/106/14/111.88
46C5678LTC345678901485-349K3Z6/15/116/14/121.86
47C5678LTC345678901485-349K3Z6/15/126/14/131.84
48C5678LTC345678901485-349K3Z12/1/126/15/241.81
49C5678LTC345678901485-349K3Z6/15/136/14/141.83
50C5678LTC345678901485-349K3Z6/15/146/14/151.82
51C5678LTC345678901485-349K3Z6/15/156/14/163.82
52C5678LTC345678901485-349K3Z6/15/166/15/245.82
53C5678LTC345678901485-349K3Z6/16/246/15/267.82
54C5678LTC34567813082786/15/166/15/24165.65
55C5678LTC34567813082786/16/246/15/26163.65
56C5678LTC345678198796-18AH6/15/106/14/1111.86
57C5678LTC345678198796-18AH6/15/116/14/129.96
58C5678LTC345678198796-18AH6/15/126/14/139.66
59C5678LTC345678198796-18AH6/15/136/14/149.47
60C5678LTC345678198796-18AH6/15/146/14/159.37
61C5678LTC345678198796-18AH1/1/236/15/2411.37
62C5678LTC345678198796-18AH6/16/246/15/2613.37
63C5678LTC4567899768446/15/166/15/242.28
64C5678LTC4567899768446/16/246/15/263.28
65C5678LTC4567893968546/15/166/15/2417.74
66C5678LTC4567893968546/16/246/15/2618.74
67C5678LTC45678912680586/15/106/14/1110.03
68C5678LTC45678912680586/15/116/14/128.42
69C5678LTC45678912680586/15/126/14/138.17
70C5678LTC45678912680586/15/136/14/148.01
71C5678LTC45678912680586/15/146/14/157.93
Sheet2
Cell Formulas
RangeFormula
L2:L7,L9:L19,L21:L25,L20:M20,L8:M8L2=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"))
 
Upvote 0
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?

SupplierContractPart NumberStart DateEnd DateRateSupplierContractPart NumberPurchase Order Create DateRate at PO Create
C5678LTC4567891268058
6/15/2010​
6/14/2011​
10.03​
C5678LTC456789
1268058​
4/29/2011​
not found
C5678LTC4567891268058
6/15/2011​
6/14/2012​
8.42​
C5678LTC456789
1268058​
4/20/2012​
not found
C5678LTC4567891268058
6/15/2012​
6/14/2013​
8.17​
C5678LTC456789
1268058​
10/2/2014​
not found
C5678LTC4567891268058
6/15/2013​
6/14/2014​
8.01​
C5678LTC456789
1268058​
5/22/2012​
not found
 
Upvote 0
JukeExcel said:
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?

SupplierContractPart NumberStart DateEnd DateRateSupplierContractPart NumberPurchase Order Create DateRate at PO Create
C5678LTC4567891268058
6/15/2010​
6/14/2011​
10.03​
C5678LTC456789
1268058​
4/29/2011​
not found
C5678LTC4567891268058
6/15/2011​
6/14/2012​
8.42​
C5678LTC456789
1268058​
4/20/2012​
not found
C5678LTC4567891268058
6/15/2012​
6/14/2013​
8.17​
C5678LTC456789
1268058​
10/2/2014​
not found
C5678LTC4567891268058
6/15/2013​
6/14/2014​
8.01​
C5678LTC456789
1268058​
5/22/2012​
not found
Click to expand...

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
 
Upvote 0
What happens if you add this bit.
Rich (BB code): 
=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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Formula for calculating count based on date or month ranges
Replies
7
Views
757
Pancakey
P
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
517
Alex Blakenburg
A
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
551
Agnarr
Agnarr
L
  • Solved
Excel Formula Multiple if then
2
Replies
12
Views
302
Lisa P NYC
L
G
  • Question
Calculating Average Price, Based on Multiple Cells
2
Replies
10
Views
598
Skybluekid
Skybluekid

Forum statistics

Threads
1,218,029
Messages
6,140,054
Members
450,257
Latest member
mlc37444

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