Need formula to return the earliest Date with the first blank "$ Amount" field that matches a specific ID

[kstrangfeld]

Hi! I have a spreadsheet that we use to track contracts, Purchase orders, and Invoices/payments. I am trying to create a field that will show me the next soonest invoice date that doesn't yet have a dollar amount populated (because that date is the next invoice that we haven't yet paid... the amount gets populated when we pay it). I've seen combinations of index, match, isblank, but can't get it to work.

Each payment has a unique ID and they tie back to an Order ID (Purchase Order). Each Order has a unique ID, and each Contract has a unique ID. Contract-->Order-->Payment. Many payments can belong to one order, and many orders can belong to one contract.

I'd like this field to live in the Contracts tab and have it look for the Contract ID, which is also in the Payment sheet as a vlookup.

Ideally I think this is something like: Look for Contract ID 1234 in the Payments sheet, then look in the Invoice Amount field for whichever rows have a blank Invoice Amount, and return the one with the next soonest date.

Any ideas? Appreciate the help!!

Karen

 

[alansidman]

Show us a sample of data with a mocked up solution. Please use XL2BB to show your example. Do not post a picture as we cannot manipulate data in a picture. Your sample should only be 8-12 records.