• 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.

Combine VLookup and SumProduct to return value based on ID and date range?

D

djphatic

New Member
Hello


I came across a situation today where I need to perform some sort of a lookup based on an ID value and between a date range. I have read some of the suggestions in the recent vlookup article and believe I need to use a vlookup combined with a sumproduct formula, though I haven't managed to figure it out.


I have uploaded a small sample spreadsheet here:


https://docs.google.com/spreadsheet/ccc?key=0AhEVZ_-0OIg6dEswSlRZOVJEZTk3bzItT09URWhJM2c#gid=0


The majority of the time the PersonID will only appear once in the data in the right table so a simple vlookup can be performed to return the OpID in this situation.


What I would like to be able to do, is when the PersonID appears more than once in the data in the right table, is firstly lookup using the PersonID and OperationDate and return the OpID if there is an exact match.


If there is not an exact match then I would like to perform a lookup that uses the OperationDate in the data in the right table and find the appropriate matching record based on the PersonID and date range between AdmissionDate and DischargeDate and return the OpID.


If this is possible i'm not exactly sure what would happen if there are multiple values, for example say the AdmissionDate and DischargeDate has a wide range of 90 days which may have multiple OperationDates from the data in the table on the right. In this instance I'd prefer the lookup to return #N/A.


I hope the above makes sense.

Any help is greatly appreciated.


I am using Excel 2007.
 
I think I've managed to answer my own question using the following formula, replacing the cells in column E:


=IF(SUMPRODUCT(--($G$2:$G$7=A2),--($H$2:$H$7<=D2)*($H$2:$H$7>=B2))=1,SUMPRODUCT(--($G$2:$G$7=A2),--($H$2:$H$7<=D2)*($H$2:$H$7>=B2),$I$2:$I$7),NA())


The formula returns the number of matches found using the PersonID and the where the OperationDate is within the range of AdmissionDate and DischargeDate. If the number of matches is 1 the same formula is calculated returning the OpID. Otherwise #N/A is returned.


Obviously the above formula can be made more presentable using named ranges.
 
You must log in or register to reply here.
Back
Top