Index Max function

[mychi11]

I am trying to use a formula index max to find the last day I did my ordering, the formula works except for one last column. Not sure why. Could any experts here kindly help?

PO Inventory Budget Est Final.xlsm
	A	B	C	D	E	F	G	H		J	K	L	M
1	PRODUCT NAME	DESCRIPTION	SUPPLIER	BRAND	CAT. NO	QTY (Exluding Expired)	Expiring Window (Days)	Stock Status		Order Quantity	Last order placed on	Unit/ Box
2	BD-1 ml Syringe	1CC SYRINGE ONLY, SLIP TIP (BOX OF 100S)	UNITED ITALIAN CORP.(HK) LTD.	BD	302100	0	30	LOW INVENTORY				100	pcs
3	BD-27G 1/2 Needle	DISPOSABLE NEEDLE 27GX1/2" (BOX OF 100S)	UNITED ITALIAN CORP.(HK) LTD.	BD	305109	0	30	LOW INVENTORY				100	pcs
4	CBS-High Security Tube	HIGH SECURITY TUBE, PACK OF 20	HARVEY, MAIN & CO.LTD	CBS	22252	0	30	LOW INVENTORY				20	pcs
5	Coda-Xtra Inline Filter (Green)	CODA XTRA INLINE FILTER (6/PK), GREEN	SURPLUS GOLDEN CHINA LTD	Coda	CODA_CXG R-012	0	30	LOW INVENTORY				1	pcs
6	Cook-17G Double Lumen	17G DOUBLE LUMEN OVUM ASPIRATION NEEDLE	COOK ASIA LTD.	Cook	K-OPSD-1735-A-L	0	30	LOW INVENTORY				1	pcs
7	Cook-17G Single Lumen	17G SINGLE LUMEN OVUM ASPIRATION NEEDLE	COOK ASIA LTD.	Cook		0	30	LOW INVENTORY				1	pcs
8	Cook-30°Holding Pipette	COOK HOLDING PIPETTE	COOK ASIA LTD.	Cook	K-HPIP-2130	20	30	IN STOCK		4	16/6/2022	10	pcs
9	Cook-30°Injection Pipette	COOK MICROINJECTION PIPETTE	COOK ASIA LTD.	Cook	K-MPIP-3130	0	30	LOW INVENTORY		2	16/6/2022	10	pcs
10	Cook-ET Catheter	GUARDIA ACCESS CURVED EMBRYO TRANSFER CATHETER	COOK ASIA LTD.	Cook	K-Jets 7019	0	30	LOW INVENTORY				1	pcs
11	Cook-ET Syringe	AIR-TITE SYRINGE (25/BOX)	COOK ASIA LTD.	Cook	K-ATS-1000	0	30	LOW INVENTORY				25	pcs
12	Cooper Surgical -PICSI Dish	SPERM SELECTION DEVICE, PICSI FOR SPERM SELECTION, 20 DISHES/ EACH	SURPLUS GOLDEN CHINA LTD	Cooper Surgical	BCT-PICSI-20	0	30	LOW INVENTORY				20	pcs
13	Cooper Surgical -Stripper tips 50µm	Cooper Surgical -Stripper tips 50µm	SURPLUS GOLDEN CHINA LTD	Cooper Surgical	OR_MXL3-IND-175	0	30	LOW INVENTORY				20	pcs
14	Cooper Surgical-Glass Pasteur Pipets (3pack)	IVF PASTEUR PIPETS PLUGGED, BOX OF 90 COTTON PLUGGED, 30 POUCHES OF 3 PIPETS EACH, 9" (22.9CM) LONG	SURPLUS GOLDEN CHINA LTD	Cooper Surgical	OR_PP-9-90PL	0	30	LOW INVENTORY				90	pcs
15	Cooper Surgical-Glass Pasteur Pipets (50pcs)		SURPLUS GOLDEN CHINA LTD	Cooper Surgical		0	30	LOW INVENTORY				90	pcs
16	Cooper Surgical-Stripper tips 135µm	STRIPPER TIPS 135UM, INDIVIDUAL PACK, 135 UM (20/PK)	SURPLUS GOLDEN CHINA LTD	Cooper Surgical	OR_MXL3-IND-135	0	30	LOW INVENTORY				20	pcs
17	Cooper Surgical-Wallace-ET catheter		SURPLUS GOLDEN CHINA LTD	Cooper Surgical		0	30	LOW INVENTORY				1	pcs
18	Cooper Surgical-Wallace-Stylet		SURPLUS GOLDEN CHINA LTD	Cooper Surgical		0	30	LOW INVENTORY				1	pcs
19	Falcon-5 ml Tubes	ROUND BOTTOM TUBES 5ML, 12 X 75MM POLYSTYRENE W/SN AP CAP. STERILE	BIO-GENE TECHNOLOGY LIMITED	Falcon	352058	0	30	LOW INVENTORY		2	16/6/2022	500	pcs
20	Falcon-Sperm Container	CORNING FALCON-CONTAINERS 4 1/2 OZ W/POLYPROPYLENE	BIO-GENE TECHNOLOGY LIMITED	Falcon	354013	0	30	LOW INVENTORY		0	0/1/1900	100	pcs
Inventory

Cell Formulas
Range	Formula
J2:J20	J2	=IFERROR(INDEX('PO Item+Eva'!$F$3:$F$9999,SUMPRODUCT(MAX(ROW('PO Item+Eva'!$E$3:$E$9999)*(Inventory!B2='PO Item+Eva'!$E$3:$E$9999))-1)),"")
K2:K20	K2	=IFERROR(INDEX('Purchased Item'!$M$3:$M$9999,SUMPRODUCT(MAX(ROW('Purchased Item'!$E$3:$E$9999)*(Inventory!B2='Purchased Item'!$E$3:$E$9999))-1)),"")
F2:F20	F2	=IF(A2<>"",P2+R2+T2+V2,"")
H2:H20	H2	=IF(F2<N2,"LOW INVENTORY","IN STOCK")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J2,A3:E80,F3:F532,G3:I80,L2:X80,K2:K215,J3:J222	Expression	=MOD(ROW(),2)=0	text	NO

PO Inventory Budget Est Final.xlsm
	A	B	C		E	F	G	H	I	J	K	L	M
1	Purchase Items
2	PO#	Vendor	Item No		Description	Qty	Currency	Price	Discount	Final Price	PO ROW	ROW	Order Date
3	1	LAP TUCK AIR CONDITIONING REFRIDGERATION ENGINEERING CO., LIMITED	-		AIR CONDITIONING SERVICE	1	HK$	3000		$3,000.00	18		13/6/2022
4	2	COOK ASIA LTD.	K-MPIP-3130		COOK MICROINJECTION PIPETTE	2	HK$	2180		$4,360.00	18		16/6/2022
5	2	COOK ASIA LTD.	K-HPIP-2130		COOK HOLDING PIPETTE	2	HK$	1500		$3,000.00	19		16/6/2022
6	3	BIO-GENE TECHNOLOGY LIMITED	352058		ROUND BOTTOM TUBES 5ML, 12 X 75MM POLYSTYRENE W/SN AP CAP. STERILE	4	HK$	750		$3,000.00	18		16/6/2022
7	3	BIO-GENE TECHNOLOGY LIMITED	354013		CORNING FALCON-CONTAINERS 4 1/2 OZ W/POLYPROPYLENE	2	HK$	560		$1,120.00	19		16/6/2022
8
9
Purchased Item

Cell Formulas
Range	Formula
L3:L7	L3	=ROW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	Expression	=MOD(ROW(),2)=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Vendor_Name

 

[Peter_SSs]

the formula works except for one last column

Actually, your formula is reading the date from the incorrect row for all the results obtained. It was just a fluke that the same date appeared in a range of cells.

Try this formula in cell K2 of 'Inventory'

=IF(B2="","",IFERROR(AGGREGATE(14,6,'Purchased Item'!M$3:M$9999/('Purchased Item'!E$3:E$9999=B2),1),""))

 

[mychi11]

thank you so much for your solution, Peter!

 

[Peter_SSs]

You're welcome. Glad to help.