Need to Filter, Match and Offset

[afinn211]

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:

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

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Material	Job	Qty	Date
2	00-0012P1	20972	21	22/05/2007	15.84	20.64
3	00-0012P2	20973	21	22/05/2007	9.69	13.3
4	5540	25887	129	21/03/2014	3.44	8.6
5	005-517-141199	29441	500	21/08/2020	0.77	0.77
6	005-520-059622	29075	113	30/01/2020	1.13	1.73
7	005-522-059624	26472	153	08/06/2015	2.87	3.37
8	005-522-059724	29071	341	30/01/2020	1.84	2.34
9	005-522-140947	30803	110	30/08/2022	not found
10	005-541-031930	31575	235	07/09/2023	not found
11	005-541-059728	29070	453	13/03/2020	not found
12	005-541-059728	29070	453	13/03/2020	not found
13	005-560-059500	30740	85	04/08/2022	not found
14	005-560-059688	30740-A	89	04/08/2022	not found
15	005-560-059689	30740-B	103	04/08/2022	not found
Sheet1

Cell Formulas
Range	Formula
E9:E15,E2:F8	E2	=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.

 

[afinn211]

Works great!! Thanks!! I need to familiarize myself with Let and Take.

Andy

 

[Fluff]

You're welcome & thanks for the feedback.