Good afternoon,
I am Using OFFICE 365.
I am really struggling with this one and not sure if it is possible.
In reference to the Sample Data Set below, on Sheet 1, I need formulas to match the Material between Sheet 1 and Sheet 2 and return the Unit Cost and Unit Price from Sheet 2 that has a quantity closest to that listed on Sheet 1 for the given Material.
Sheet 1 has only one instance of each Material, while Sheet 2 has multiple instances of each Material with different Unit Cost and Unit Price for a given quantity.
Example:
For Material: 00-0012P1, the formulas should return:
Unit Cost: 15.84
Unit Price: 20.64
Since Sheet 1, Quantity for that Material is 21 and the closest Sheet 2, Quantity listed for that Material is 20.
Sample Data Set:
Sample Sheet 1:
Sample Sheet 2:
I greatly appreciate any help!!
Andy
I am Using OFFICE 365.
I am really struggling with this one and not sure if it is possible.
In reference to the Sample Data Set below, on Sheet 1, I need formulas to match the Material between Sheet 1 and Sheet 2 and return the Unit Cost and Unit Price from Sheet 2 that has a quantity closest to that listed on Sheet 1 for the given Material.
Sheet 1 has only one instance of each Material, while Sheet 2 has multiple instances of each Material with different Unit Cost and Unit Price for a given quantity.
Example:
For Material: 00-0012P1, the formulas should return:
Unit Cost: 15.84
Unit Price: 20.64
Since Sheet 1, Quantity for that Material is 21 and the closest Sheet 2, Quantity listed for that Material is 20.
Sample Data Set:
Sample Sheet 1:
Material | Job | Qty | Date |
00-0012P1 | 20972 | 21 | 5/22/2007 |
00-0012P2 | 20973 | 21 | 5/22/2007 |
0005540 | 25887 | 129 | 3/21/2014 |
005-517-141199 | 29441 | 500 | 8/21/2020 |
005-520-059622 | 29075 | 113 | 1/30/2020 |
005-522-059624 | 26472 | 153 | 6/8/2015 |
005-522-059724 | 29071 | 341 | 1/30/2020 |
005-522-140947 | 30803 | 110 | 8/30/2022 |
005-541-031930 | 31575 | 235 | 9/7/2023 |
005-541-059728 | 29070 | 453 | 3/13/2020 |
005-541-059728 | 29070 | 453 | 3/13/2020 |
005-560-059500 | 30740 | 85 | 8/4/2022 |
005-560-059688 | 30740-A | 89 | 8/4/2022 |
005-560-059689 | 30740-B | 103 | 8/4/2022 |
Sample Sheet 2:
Material | Description | Quote Qty | Unit Cost | Unit Price |
00-0012P1 | PULLEY, CWT. CABLE WITH HOLES | 10 | 28.1 | 35.96 |
00-0012P1 | PULLEY, CWT. CABLE WITH HOLES | 20 | 15.84 | 20.64 |
00-0012P1 | PULLEY, CWT. CABLE WITH HOLES | 300 | 6.48 | 7.56 |
00-0012P2 | PULLEY, CWT. CABLE | 10 | 17.38 | 23 |
00-0012P2 | PULLEY, CWT. CABLE | 20 | 9.69 | 13.3 |
00-0012P2 | PULLEY, CWT. CABLE | 800 | 3.28 | 5.61 |
0005540 | WASHER SPECIAL 8mm AL | 50 | 5.84 | 11.9 |
0005540 | WASHER SPECIAL 8mm AL | 125 | 3.44 | 8.6 |
0005540 | WASHER SPECIAL 8mm AL | 1000 | 2.09 | 5.4 |
0005540 | WASHER SPECIAL 8mm AL | 2000 | 2 | 4.9 |
005-517-141199 | BUSHING, ALUMINUM | 500 | 0.77 | 0.77 |
005-517-141199 | BUSHING, ALUMINUM | 1000 | 0.72 | 0.56 |
005-520-059622 | SHAFT, U13 DRUM ( 81/4 LONG) | 100 | 1.13 | 1.73 |
005-520-059622 | SHAFT, U13 DRUM ( 81/4 LONG) | 200 | 0.87 | 1.33 |
005-522-059624 | G37 MOTOR PULLEY | 100 | 2.87 | 3.37 |
005-522-059724 | MOTOR PULLEY | 100 | 3.18 | 3.37 |
005-522-059724 | MOTOR PULLEY | 200 | 2.18 | 2.92 |
005-522-059724 | MOTOR PULLEY | 300 | 1.84 | 2.34 |
I greatly appreciate any help!!
Andy