Multi-Criteria Date find/lookup/Index?

[Tyrate]

I have 2 tables on separate worksheets in the same workbook, the 1^st table (TableA) has the Workorders in which an item has been assigned too, the 2^nd table (TableB) shows the items on the vendor’s purchase order record table.

TableA

[TABLE="width: 588"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]WO#[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]QTY in Stock[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]0014598[/TD]
[TD]55698-RC-04[/TD]
[TD]SCREW, 6-32 X 5/16 FLAT, TORX,[/TD]
[TD]46[/TD]
[TD]B17N[/TD]
[/TR]
[TR]
[TD]0016985[/TD]
[TD]1148999-00-3[/TD]
[TD]ASSY, BP/Tx FILTER,[/TD]
[TD]333[/TD]
[TD]FG2B[/TD]
[/TR]
[TR]
[TD]0018562[/TD]
[TD]223045-177[/TD]
[TD]BRKT, EXTENSION, TX FILTER[/TD]
[TD]0[/TD]
[TD]A23B[/TD]
[/TR]
[TR]
[TD]0018869[/TD]
[TD]86359-C6-FC-01[/TD]
[TD]ASSY, CABLE, MMCX M STR-SMA[/TD]
[TD]12[/TD]
[TD]C12D[/TD]
[/TR]
[TR]
[TD]0016980[/TD]
[TD]1456823[/TD]
[TD]CISCO, ROUTER[/TD]
[TD]1[/TD]
[TD]D08C
[/TD]
[/TR]
</tbody>[/TABLE]


TableB

A	B	C	D	E	F
PO#	Component ID	Manufacturer	Required by Date	QTY Ordered	QTY Received
00018475	1456823	CISCO	2/19/19	35	35
00018482	25978-121-CA	MacMasters	3/1/19	25	20
00019201	1456823	CISCO	3/4/19	20	0
00019223	223045-177	Miller Machine	2/28/19	30	30
00019642	1148999-00-3	AD Electrical	3/12/19	100	100
00020101	1456823	CISCO	3/15/19	15	0
00020147	223045-177	Miller Machine	3/18/19	30	15
00020358	223045-177	Miller Machine	3/30/19	15	0

<tbody>
</tbody>

What I’m trying to accomplish?
I would like to find the applicable “required by date” on tableB, that matches the “Item” column on TableA with the “Component ID” column on tableB, evaluate the records, then from tableB grab the 1^st “required by date” where either the “purchase quantity received” is less than the quantity ordered or equal to “0”.

What it should look like:

TableA (mod)

[TABLE="width: 660"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]WO#[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]QTY in Stock[/TD]
[TD]Location[/TD]
[TD]Expected
Delivery[/TD]
[/TR]
[TR]
[TD]0014598[/TD]
[TD]55698-RC-04[/TD]
[TD]SCREW, 6-32 X 5/16 FLAT, TORX,[/TD]
[TD]46[/TD]
[TD]B17N[/TD]
[TD]3/18/19[/TD]
[/TR]
[TR]
[TD]0016985[/TD]
[TD]1148999-00-3[/TD]
[TD]ASSY, BP/Tx FILTER,[/TD]
[TD]333[/TD]
[TD]FG2B[/TD]
[TD]4/12/19[/TD]
[/TR]
[TR]
[TD]0018562[/TD]
[TD]223045-177[/TD]
[TD]BRKT, EXTENSION, TX FILTER[/TD]
[TD]0[/TD]
[TD]A23B[/TD]
[TD]3/18/19[/TD]
[/TR]
[TR]
[TD]0018869[/TD]
[TD]86359-C6-FC-01[/TD]
[TD]ASSY, CABLE, MMCX M STR-SMA[/TD]
[TD]12[/TD]
[TD]C12D[/TD]
[TD]4/2/19[/TD]
[/TR]
[TR]
[TD]0016980[/TD]
[TD]1456823[/TD]
[TD]CISCO, ROUTER[/TD]
[TD]1[/TD]
[TD]D08C[/TD]
[TD]3/4/19[/TD]
[/TR]
</tbody>[/TABLE]


Note, that the Item number can be called out several times on the purchase order, with different quantiles that were ordered and received. I thought it would be a fairly simple formula to figure out but I’ve tried several types of excel functions like INDEX/MATCH and VLOOKUP with little to no results to from it, of course, this is most likely because of human error since I’m an advanced novice at best when it comes to excel and formulas.

Whatever help I can get would be greatly appreciated!

 

[RasGhul]

Hi Tyrate,

Can use this? It seems to work with the data I have available;

Book1
	A	B	C	D	E	F
1	WO#	Item	Description	QTY in Stock	Location	Expected
2	14598	55698-RC-04	SCREW, 6-32 X 5/16 FLAT, TORX,	46	B17N
3	16985	1148999-00-3	ASSY, BP/Tx FILTER,	333	FG2B
4	18562	223045-177	BRKT, EXTENSION, TX FILTER		A23B	18-03-19
5	18869	86359-C6-FC-01	ASSY, CABLE, MMCX M STR-SMA	12	C12D
6	16980	1456823	CISCO, ROUTER	1	D08C	04-03-19
TableA

Cell Formulas
Range	Formula
F2	{=MIN(IF(TableB!$B$2:$B$9=B2,TableB!$G$2:$G$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D	E	F	G
1	PO#	Component ID	Manufacturer	Required by Date	QTY Ordered	QTY Received	Test
2	18475	1456823	CISCO	19-02-19	35	35
3	18482	25978-121-CA	MacMasters	01-03-19	25	20	01-03-19
4	19201	1456823	CISCO	04-03-19	20	0	04-03-19
5	19223	223045-177	Miller Machine	28-02-19	30	30
6	19642	1148999-00-3	AD Electrical	12-03-19	100	100
7	20101	1456823	CISCO	15-03-19	15	0	15-03-19
8	20147	223045-177	Miller Machine	18-03-19	30	15	18-03-19
9	20358	223045-177	Miller Machine	30-03-19	15	0	30-03-19
TableB

Cell Formulas
Range	Formula
G2	=IF(OR(F2),D2,"")