Need help with VLookup to complete variance analysis

Hi All,

This is my first post on this forum. I am working on automating the variance analysis report for my company by using excel formulas. I have attached a workbook for you all to work on, plus it will help everyone to better understand my problem. So, here it is:

Workbook "Excel Forum" (See Attached file) contains data in two worksheets "Sheet1" & "Sheet2". I am trying to use different excel functions to use the record number in "sheet1" and pull the total patient paid amount from "sheet2" in respective certification period in "Sheet1". Example: Lets say I am trying to pull paid amount for record number "4094".

Now there are few problems: First, "Sheet2" contains multiple entries for record number 4094. Second, the "service date" in "sheet2" should be within the certification period in "sheet1".

I need help to figure out a formula which will lookup for record number from "Sheet1" in "Sheet2". Then if the service date lies within the certification period, it would add all the received amounts and display the total sum in Total Received Amount in "Sheet1". I would really appreciate if someone can take sometime and help me outwith this problem. Thanks
Excel Forum.xlsx

Thanks NBVC. I tried the formula that you gave me and it did work. Thanks a ton and appreciate your help.

Hi Rahulsharma

Perhaps you need to update your profile regarding your Excel version!