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

VLOOKUP Data based on multiple criteria

M

Mindtriks

New Member
Hey Guys - New to this forum, but I am having a lot of difficulty figuring out the code I want for a specific assignment.

I am working on a spreadsheet with two tabs. On tab one I have a list of doctor visits by patient with columns showing the ID number of the physician and the date the checkup occurred. On the second tab I have a list of contracted rates for each physican, but the rates vary depending on time frame (we usually contract for 6 months, 1 year, etc).

I want to match the ID number from tab 1 then go to tab 2 and find the associated physican, then go back to tab one find the start state of the doctor visit and then go back to tab 2 and then perform a vlookup for values on tab 2 (Columns D-F) based on when this individual saw the doctor (see which time frame the visit falls under and then perform the vlookup function on the appropriate row)
 

Attachments

Last edited:
Hi:

Your requirement is bit confusing for me. This is what I understood.

You want to bring column D-F on sheet 2 to sheet one, matching the ID number of sheet 1 to sheet 2 and also if the start date in sheet 1 falls with in the range of contract start date and end date on sheet 2. Let me know if this is what you are looking for.


Thanks
 
Hello,

I totally agree with Nebu's reply..and need a bit confirmation on it. As i tried working on it but not coming up with the expected results.. Pls see the file attached.
 

Attachments

Nebu - You are correct. I am looking to bring the values from columns D-F on sheet 2 to columns 3-5 on sheet 1 and make sure the values brought over are the ones that correspond with the appropriate contract date. For example, I would look at the ID Number (12450079) on Sheet 1, Column A, Row 2. I would then go to Sheet 2 and find all the rows that had that matching ID Number. Then I would go back to Sheet 1 looking at Column B, Row 2 to see the Contract start date. Then from the filtered results on Sheet 2 I would want to grab the values in Columns D-F for which row the contract start dates falls within (See Columns B and C). Please let me know if that helps.
 
I am currently working with this formula, but am failing at the looking within a range part.

=INDEX(Sheet2!D:D,MATCH(1,(Sheet1!A2=Sheet2!A:A)*(Sheet1!B2=Sheet2!B:B),0))
 
Mindtriks, there is a solution that would work if the quality of your data allowed for the result you're asking (addressed below).

Solution: Insert a helper column in Sheet2, Column D with formula =VALUE(A2&B2). Sort the Sheet2 table by Column D, Ascending Order. Then, in Sheet1, C2, insert the formula =VLOOKUP(VALUE(A2&B2),Sheet2!D:G,2,TRUE) and repeat for D2 and E2 with the correct column index numbers.

The data quality issue I mentioned is that your date ranges do not allow for unique or even expected results. This is due to two reasons:
1) I filtered on the first ID Number 12450002 and can immediately see that almost all ranges have a Contract End Date which is later than the next Contract Start Date. So for my example, there is more than one valid row of data for 8/8/2012 because that date is contained within multiple contract date ranges. How do you know which one to pick?
2) You have multiple contract date ranges that end on 12/31/9999 for the example I pulled. When resorting the data by Column D, you are guaranteed to run into issues if those rows are in your table.
 
I was able to solve the issue with the following formula:


=INDEX(Sheet2!D:D,MATCH(1,(Sheet1!A2=Sheet2!A:A)*(IF(Sheet1!B2>=Sheet2!B:B,IF(Sheet1!B2<=Sheet2!C:C,1))),0))
 
You must log in or register to reply here.
Back
Top