Return date against validating if the bill amount equals to payment and return a date

Hello Dear Experts,

I am looking for a Big help here. I tried several if functions but of no avail. Here is what I wanted to do. I have "Automated Invoices "Tab in the Excel sheet. Now, In this for every client I have an Invoice. That is all detailed under CREDIT column of (LEDGER) tab. Thus,I am looking for a validation that returns the date at which the invoice is being fully paid. That exact date I am looking.

For example.

Click on Automated Invoice " You see Brake Shoe item equivalent 10000/- of value amount. Now I want this value to be checked under "LEDGER" tab.

2 Click on Ledger Tab, if you see Debit amounts, you will realize that this client Makki, paid off complete 10000 on 25th thus I want to see this 25 Jun-18 on Column Z namely Date column under "AUTOMATED INVOICE" TAB.

Similary if we move further on Automated Invoice I want to "Wheel Hub " item 55000/- amount fully consumed under Debit column of 27 Jun-18. Thus this date should show up under Z column.

In simple words I would like a function that equates all Debits(PAYMENTS), with respect to invoice value under tab Automated Invoice for each of items. This help me to know which item was being fully transacted and paid on what date(DEBIT- PAYMENTS) from the LEDGER sheet . I Hope I explained it well. Please feel free to ask more. I would be more than willing to explain. In short this is transaction completion date of all my invoice to know how many days from the billing to payment ledger dates my client is taking. Thanks

So I have some questions,

I am looking for a validation that returns the date at which the invoice is being fully paid

1) Where am I looking to see the date and what indicates that the invoice is fully paid?
2) When I find the information in question 1, where are you looking for the formula to populate it to? Is that col Z in the automated invoice tab?
OR, am I supposed to use the status columns in the automated invoice tab to pull the date (which date (column location) from which tab (column location)) and put it where?
Sometimes putting in expected results into a workbook is a big help. Or in this case, telling me what the anticipated results are and in which tab and column and where it comes from and under which circumstances. So, if this value is populated in this cell in this tab I want this value to show up in this cell in this tab.
We'll get you there.

BTW, while you are thinking about it, I'd consider getting rid of NOW() in cell I1 of the automated invoice tab and replace it with TODAY(). Now updates every time you double click on a cell, hit enter or save because it gives you the date and time to the second. While today will give you today's date and only update once that day.
Second, you don't need the "+" plus sign in front of formulas, I'm told that is a left over from very old coding. So you can remove it from any formulas that begin as =+FORMULA.
Third, in your ledger sheet in col V you have the wrong "" (quotes) in the formula so it is returning #NAME? You need to replace with the regular quotes and then the formula will work and leave blanks where you want them.

I am leaning to your thinking that N3 under Automated Invoice Tab is in itself appropriate place to locate the DATE

If you are planning to move things around in your sheet you ought to upload another current workbook so formulas point to the correct places.
AND, highlight the area or areas you need formulas in PLUS provide some expected results and where they come from.

Hello Sir,

I have added a new sheet with more data points. Please note Z column is most appropriate now by rechecking formula this may disturb the outstanding time of the overdue bills. Thus Z column is where i need expected result that validates the amount total vs payments under Ledger.

Please check as I have also added expected results. Please let me know if anything is confusing. Thanks much!!!

sorry but I cannot open that workbook, I'm getting an error when I try. Maybe reload it?

Ok, attached is your workbook back again. I tried to clean up your formulas with removing the NOW() and changing to TODAY(), fixed the formula in col V of the CL LEDGER, got rid of the plus sign before the formulas where it wasn't needed.
Wasn't sure which item to point to in the CL LEDGER tab, sometimes the item I was checking from col H in Automated Invoice tab was in col D of the CL Ledger and other times it was in col B of the CL Ledger (examples rows 9, 11, 12 and 13 to note a few). So in order to match 3 variables, date, item and party name and pull back the date (which didn't always match what you entered (MAKKI AUTOS you note paid date as 25-Jun-18 but I don't see that anywhere in the CL Ledger tab but do see 26-Jun-18). So in order to match the three variables I used a sumproduct formula in col AA that you can look at and adapt if you need to.

As a note, if you had a unique invoice for each OVERALL transaction - rather than for example 1A and 1B for MAKKI Autos two same day orders, and then put that invoice number in the ledger and invoice tabs the lookup would be much easier without trying to match three variables, it would only need one variable.

first (your post #11) if I am pointing at the wrong column to return (it is the section in the formula 'CL LEDGER'!$L$2:$L$206) you can repoint it to the correct date column in CL ledger.
second, in the CL Ledger tab for MAKKI autos and Brake shoe, there is NO 25-Jun-18 date anywhere in the CL Ledger tab that I can find. The only date close to that is the 26-Jun-18 date in column L. If you want the date returned from column A then change this 'CL LEDGER'!$L$2:$L$206 to this 'CL LEDGER'!$A$2:$A$206
For post #12, what indicates to me that a partial payment was made and where is the date to pull for that?

If I'm understanding what you want, I don't know how to pull over those dates. It sounds like you are saying that you want the date pulled over from A4 for MAKKI autos but there is nothing else to match against. Using any index/match formula to pull over the date across from MAKKI autos, it will only pull over the first value it finds and there is nothing else to match against. And the first value it will find is 23-Jun-18, unless I use the CHQ date column, then it will pull over the 26-Jun-18 date, and it will pull over that date for every transaction for MAKKI autos without adding more items to match against.
If you had a unique invoice number that was in both the ledger and automated invoice tabs then a match could be made.

Maybe someone else will be able to help you more than I can, I cannot think of a way to pull over multiple specific dates when I can only match against one value.