• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Required

V

vmohan1978

Member
Dear All,
I am looking for a lookup and match formula,
In sheet 1, I want a formula for invoiced amount and payment received month wise , where the data is in sheet 2 as per the attachment .

Not getting how to proceed with the formula , tried Vlook didn't succeed.
 

Attachments

  • Formula Required.xlsx
    22.2 KB · Views: 7
Thank you very much ,Deepak and Misra.

Deepak , Invoiced amount is matching perfectly , but the payment received shows the same month of invoiced amount , it should be 1 month later of invoiced amount.
Misra , your formula worked perfectly , thank you very much it saved my lot of time .
Once again thanks for both .
 
Dear Misra,
In formula , what ("zzzzzz" indicates?


IFERROR(INDEX(Sheet7!$G$4:$G$44,MATCH(1,(LOOKUP("zzzzzz",$C$2:$C7)=Sheet7!$D$4:$D$20)*(Sheet4!G$1=Sheet7!$I$4:$I$20),0)),"")
 
Dear Misra ,

If 2 or 3 payments made on the same month , formula is considering the first payment transaction only, not the summation of all transaction in the month for that particular heading.
 
In the attached the sheet the total sum for the Travels in the month of Aug-14 it should be 329627, but formula shows only 2385.
 

Attachments

  • Copy of Formula Required.xlsx
    26.3 KB · Views: 4
Hi Mohan,

See the file. Formula changed to SUMPRODUCT keeping some component same.

Regards,
 

Attachments

  • Copy of Formula Required.xlsx
    25 KB · Views: 2
You must log in or register to reply here.
Back
Top