Need to Filter, Match and Offset

afinn211

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

MaterialJobQtyDate
00-0012P120972
21​
5/22/2007​
00-0012P220973
21​
5/22/2007​
000554025887
129​
3/21/2014​
005-517-14119929441
500​
8/21/2020​
005-520-05962229075
113​
1/30/2020​
005-522-05962426472
153​
6/8/2015​
005-522-05972429071
341​
1/30/2020​
005-522-14094730803
110​
8/30/2022​
005-541-03193031575
235​
9/7/2023​
005-541-05972829070
453​
3/13/2020​
005-541-05972829070
453​
3/13/2020​
005-560-05950030740
85​
8/4/2022​
005-560-05968830740-A
89​
8/4/2022​
005-560-05968930740-B
103​
8/4/2022​

Sample Sheet 2:

MaterialDescriptionQuote QtyUnit CostUnit Price
00-0012P1PULLEY, CWT. CABLE WITH HOLES
10​
28.1​
35.96​
00-0012P1PULLEY, CWT. CABLE WITH HOLES
20​
15.84​
20.64​
00-0012P1PULLEY, CWT. CABLE WITH HOLES
300​
6.48​
7.56​
00-0012P2PULLEY, CWT. CABLE
10​
17.38​
23​
00-0012P2PULLEY, CWT. CABLE
20​
9.69​
13.3​
00-0012P2PULLEY, CWT. CABLE
800​
3.28​
5.61​
0005540WASHER SPECIAL 8mm AL
50​
5.84​
11.9​
0005540WASHER SPECIAL 8mm AL
125​
3.44​
8.6​
0005540WASHER SPECIAL 8mm AL
1000​
2.09​
5.4​
0005540WASHER SPECIAL 8mm AL
2000​
2​
4.9​
005-517-141199BUSHING, ALUMINUM
500​
0.77​
0.77​
005-517-141199BUSHING, ALUMINUM
1000​
0.72​
0.56​
005-520-059622SHAFT, U13 DRUM ( 81/4 LONG)
100​
1.13​
1.73​
005-520-059622SHAFT, U13 DRUM ( 81/4 LONG)
200​
0.87​
1.33​
005-522-059624G37 MOTOR PULLEY
100​
2.87​
3.37​
005-522-059724MOTOR PULLEY
100​
3.18​
3.37​
005-522-059724MOTOR PULLEY
200​
2.18​
2.92​
005-522-059724MOTOR PULLEY
300​
1.84​
2.34​




I greatly appreciate any help!!

Andy
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
How about
Fluff.xlsm
ABCDEF
1MaterialJobQtyDate
200-0012P1209722122/05/200715.8420.64
300-0012P2209732122/05/20079.6913.3
455402588712921/03/20143.448.6
5005-517-1411992944150021/08/20200.770.77
6005-520-0596222907511330/01/20201.131.73
7005-522-0596242647215308/06/20152.873.37
8005-522-0597242907134130/01/20201.842.34
9005-522-1409473080311030/08/2022not found
10005-541-0319303157523507/09/2023not found
11005-541-0597282907045313/03/2020not found
12005-541-0597282907045313/03/2020not found
13005-560-059500307408504/08/2022not found
14005-560-05968830740-A8904/08/2022not found
15005-560-05968930740-B10304/08/2022not found
Sheet1
Cell Formulas
RangeFormula
E9:E15,E2:F8E2=LET(f,FILTER(Sheet2!$C$2:$E$100,Sheet2!$A$2:$A$100=A2,"not found"),TAKE(SORTBY(f,ABS(C2-INDEX(f,,1))),1,-2))
Dynamic array formulas.
 
Upvote 0
Solution
Works great!! Thanks!! I need to familiarize myself with Let and Take.

Andy
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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