Lookup and match part lines to a purchase order number

[ac7]

Hello -

I have two worksheets, "Parts" and "Purch".

Parts:

A​	B​	C​	D​
1​	Part	Qty	Short?
2​	123456	5	N
3​	123456	3	Y
4​	123456	5	Y
5​	123456	5	Y

Purch:

A​	B​	C​	D​	E​	F​	G​	H​
1	PO	Line	Part	Qty	Promised	Expected	D?
2	123	1	123456	3	8/11/2023	8/14/2023
3	123	2	123456	3	8/18/2023	8/18/2023
4	456	1	123456	2	8/25/2023	8/30/2023
5	789	1	123456	5		9/1/2023
6	1001	1	123456	5	9/8/2023	9/8/2023	D
7	1010	1	123456	5	9/15/2023	9/15/2023

I am looking for a way to lookup/match a part from the Parts worksheet and find the next purchase order, line, and expected date on the "Purch" worksheet that can satisfy the Parts line. Conditions:

• If Short? on the Parts worksheet is "N", then no lookup needed.
• If Promised on the Purch worksheet is blank, then don't use this purchase order in the results. Go to the next purchase order.
• If D? on the Purch worksheet has a "D", then don't use this purchase order in the results. Go to the next purchase order.
• I have some lines that might be satisfied by more than one purchase order. Is it possible to put multiple matches into the return cell? If not, then pull the last purchase order, line, and expected date that will satisfy the PO.

Here is what I am expecting for results:

A​	B​	C​	D​	E​	F​	G​
1	Part	Qty	Short?	PO	Line	Expected
2	123456	5	N
3	123456	3	Y	123	1	8/14/2023
5	123456	5	Y	123 456	2 1	8/18/2023 8/30/2023
5	123456	5	Y	1010	1	9/15/2023

I wasn't sure if this is best handled by a formula or VBA. I am open to any and all ideas. I have thousands of parts that I am looking to assign purchase order numbers to. Thank you!

 

[ac7]

Bump. Hoping for any suggestions to help me accomplish this.