# How to use formula to return delivery date

#### sengleong98

##### New Member
Hi,
I am new to the forum and are desperately in need of help.
Basically, I created 2 sheets:-
1) Purchase Order Tracking (PO)
2) Material Inventory (MTX)
Sheet (PO) is used to store each PO issued in every row. I will manually key in the vendor commit date and qty..
I use sheet (MTX) to calculate the usage of 300 types of parts (300 part numbers(P/N)). If a P/N has insufficient qty., I can use sumproduct to lookup the total outstanding PO qty. in sheet (PO). The formula in sheet (MTX) looks like this:-
=SUMPRODUCT(('PO'!\$C\$3:\$C\$1202=MTX!\$C4)*(\$C4<>"")*(MTX!\$AQ4<=0)*('PO'!\$AC\$3:\$AC\$1202="")*('PO'!\$N\$3:\$N\$1202))
- Column C in both sheets refers to P/N.
- Column AC in sheet (PO) refers to the actual received date.
- Column N in sheet (PO) refers to the PO qty.
- Column AQ in sheet (MTX) refers to the balance qty.
So, the above formula is just:-
sumproduct((sheet(PO)P/N matches sheet(MTX)P/N) and (sheet(MTX)P/N is not blank) and (balance qty. is <=0) and (sheet(PO)received date = blank (meaning part haven't received), sum(sheet(PO)PO qty.))
This is the easy part...
Now, I need to use formula(s) to find out the 1st commit date in 1 column and then 2nd commit date in another column... that is more than I can handle...
I used =IF(\$AS4>0,VLOOKUP(C4,'Mthly PO'!\$C:\$AA,25,0),"") but this is not correct because it will always return the 1st match (even if the PO had already closed). BTW, sheet (MTX)AS is the column I use to calculate the total PO qty..
I am still searching through this forum for answer, but would think asking the question might get faster result.
Please help...

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Replies
11
Views
306
Replies
3
Views
333
Replies
5
Views
1K
Replies
2
Views
254
Replies
1
Views
247

Threads
1,196,020
Messages
6,012,902
Members
441,739
Latest member
Jeezer

### 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

### 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