VLOOKUP Multiple Criteria Advanced

[Mindtriks]

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)


First table is Sheet 1 (Top Table), Second Table is Sheet 2 (Bottom Table)

ID Number 12450079 12450079 12450079 12450788 12450788 12450788 12450788 12450788 12450788 12450788 12450788 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450237 12450046 12450046 12450011 12450046 12450046 12450046 12450046 12450046 12450046 12450046 12450046 12450046 12450046 12450046 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 12450119 <colgroup><col></colgroup><tbody> </tbody>

Start Date 6/1/2015 5/1/2015 5/30/2015 1/11/2015 4/22/2015 5/31/2015 1/17/2015 1/24/2015 2/11/2015 5/16/2015 3/29/2015 6/6/2015 1/29/2015 3/10/2015 3/17/2015 3/31/2015 4/15/2015 1/27/2015 3/3/2015 3/23/2015 4/4/2015 2/24/2015 4/3/2015 4/19/2015 5/9/2015 5/16/2015 2/19/2015 4/4/2015 5/21/2015 1/29/2015 4/4/2015 4/23/2015 3/3/2015 4/14/2015 4/15/2015 4/16/2015 1/28/2015 6/8/2015 2/9/2015 1/29/2015 1/21/2015 5/14/2015 5/25/2015 3/6/2015 3/26/2015 5/6/2015 5/15/2015 1/28/2015 5/20/2015 3/4/2015 4/15/2015 2/25/2015 5/31/2015 4/29/2015 4/23/2015 6/10/2015 3/28/2015 5/7/2015 6/17/2015 6/19/2015 6/7/2015 2/9/2015 4/19/2015 3/23/2015 5/30/2015 2/9/2015 3/22/2015 4/2/2015 6/5/2015 5/22/2015 1/22/2015 5/14/2015 2/18/2015 5/18/2015 2/2/2015 6/17/2015 5/16/2015 5/4/2015 5/27/2015 6/8/2015 5/3/2015 5/24/2015 6/8/2015 5/5/2015 5/5/2015 5/27/2015 5/19/2015 5/14/2015 5/25/2015 5/31/2015 6/5/2015 4/25/2015 5/22/2015 5/30/2015 5/14/2015 4/4/2015 5/10/2015 6/19/2015 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

ID Number Contract Start Date Contract End Date CF DNE DSC 12451359 10/3/2015 1/2/2012 1 1 9 12451302 1/3/2012 4/2/2012 1 0 5 12451320 1/3/2012 4/2/2012 1 1 8 12451330 1/3/2012 4/2/2012 1 0 0 12451332 1/3/2012 4/2/2012 0 0 5 12451339 1/3/2012 4/2/2012 0 0 0 12451341 1/3/2012 4/2/2012 0 1 3 12451344 1/3/2012 4/2/2012 0 1 11 12451356 1/3/2012 4/2/2012 1 1 0 12451361 1/3/2012 4/2/2012 0 0 3 12451370 1/3/2012 4/2/2012 1 1 2 12451374 1/3/2012 4/2/2012 0 1 2 12450296 4/22/2012 5/2/2012 1 0 7 12450558 4/22/2012 5/2/2012 1 0 3 12450565 3/20/2012 5/15/2012 1 1 10 12450119 4/3/2012 5/16/2012 1 0 9 12450209 4/3/2012 5/16/2012 1 1 2 12450085 5/16/2012 5/26/2012 1 0 9 12450597 3/20/2012 5/31/2012 1 0 4 12450076 1/11/2012 6/1/2012 1 1 7 12450029 4/22/2012 6/2/2012 0 1 2 12450203 4/22/2012 6/2/2012 1 1 3 12450018 3/10/2012 6/6/2012 0 1 9 12450754 2/8/2012 6/12/2012 1 0 3 12450537 3/9/2012 6/15/2012 0 1 11 12450299 4/22/2012 6/16/2012 1 1 6 12450147 4/22/2012 7/2/2012 0 1 0 12670056 5/23/2012 7/2/2012 0 1 4 12450152 6/17/2012 7/10/2012 0 1 2 12450152 7/11/2012 7/16/2012 0 1 8 12450596 4/22/2012 7/16/2012 0 0 8 12450484 4/22/2012 8/2/2012 0 0 6 12450023 6/2/2012 8/7/2012 1 1 7 12450034 5/11/2012 8/7/2012 1 1 6 12450040 5/31/2012 8/7/2012 1 0 8 12450042 4/18/2012 8/7/2012 1 1 8 12450051 4/4/2012 8/7/2012 0 0 5 12450052 3/31/2012 8/7/2012 0 1 2 12450076 6/2/2012 8/7/2012 0 0 8 12450087 4/22/2012 8/7/2012 0 0 11 12450124 6/2/2012 8/7/2012 0 0 6 12450176 7/11/2012 8/7/2012 0 0 4 12450196 4/29/2012 8/7/2012 0 1 9 12450235 4/21/2012 8/7/2012 1 0 6 12450347 6/24/2012 8/7/2012 1 1 4 12450388 6/16/2012 8/7/2012 1 1 1 12450389 3/23/2012 8/7/2012 0 0 0 12450403 5/27/2012 8/7/2012 1 1 4 12450411 3/18/2012 8/7/2012 0 1 2 12450460 3/22/2012 8/7/2012 0 0 2 12450488 5/11/2012 8/7/2012 1 0 4 12450497 4/4/2012 8/7/2012 0 0 11 12450508 3/30/2012 8/7/2012 0 0 1 12450563 4/18/2012 8/7/2012 1 0 2 12450647 4/20/2012 8/7/2012 0 1 7 12450669 4/19/2012 8/7/2012 1 1 1 12450670 5/9/2012 8/7/2012 0 0 4 12450672 2/9/2012 8/7/2012 0 0 7 12450697 6/22/2012 8/7/2012 0 0 7 12450709 6/3/2012 8/7/2012 1 0 8 12450723 4/5/2012 8/7/2012 0 1 1 12450742 4/25/2012 8/7/2012 0 0 4 12450832 6/2/2012 8/7/2012 1 1 9 12450839 4/22/2012 8/7/2012 0 1 9 12450867 4/27/2012 8/7/2012 0 1 5 12450884 4/19/2012 8/7/2012 1 0 5 12670023 4/12/2012 8/7/2012 1 0 7 12450002 4/21/2012 8/9/2012 1 0 11 12450774 1/5/2012 8/10/2012 0 0 8 12450119 5/17/2012 8/16/2012 0 0 11 12450209 5/17/2012 8/16/2012 0 1 6 12450272 7/11/2012 8/16/2012 0 1 11 12450447 4/22/2012 8/16/2012 0 0 9 12450677 7/11/2012 8/16/2012 0 0 3 12450688 4/22/2012 8/16/2012 0 0 8 12450104 8/8/2012 8/21/2012 1 1 0 12450033 8/8/2012 8/22/2012 1 0 11 12450099 8/8/2012 8/23/2012 0 1 1 12450271 8/8/2012 8/25/2012 0 0 10 12450010 8/8/2012 8/26/2012 0 1 0 12450051 8/8/2012 8/28/2012 1 1 8 12450211 8/8/2012 8/28/2012 1 0 3 12450280 8/8/2012 8/29/2012 0 1 0 12450723 8/8/2012 8/30/2012 0 0 4 12450097 8/8/2012 8/31/2012 0 0 7 12450162 8/8/2012 8/31/2012 0 1 10 12450643 4/3/2012 9/2/2012 1 0 5 12450702 4/22/2012 9/2/2012 1 1 10 12450064 8/8/2012 9/5/2012 1 1 6 12450200 8/8/2012 9/5/2012 0 1 11 12450604 8/8/2012 9/5/2012 1 0 5 12450615 8/8/2012 9/5/2012 1 1 0 12450797 5/25/2012 9/5/2012 1 0 6 12450845 8/8/2012 9/5/2012 1 0 3 12450890 8/8/2012 9/5/2012 0 1 6 12450844 8/8/2012 9/6/2012 1 0 6 12450847 8/8/2012 9/8/2012 1 0 5 12450644 8/8/2012 9/11/2012 0 1 11 <colgroup><col><col><col><col><col span="2"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Latchmaker]

Using Sumproduct will render the results you seek.
This formula uses Sheet1 (Physician Contract Data) and Sheet2 (Patient Data) as Sheet Names replace accordingly also the range is for up to 5000 rows of data that can be adjusted as well.
This formula will return the results for Column D You can change the D Range to E, F,or G for which ever column you would like to pull from.
=SUMPRODUCT(--((Sheet1!$A$1:$A$5000=Sheet2!A2)),--((Sheet1!$B$1:$B$5000< Sheet2!B2)),--((Sheet1!$C$1:$C$5000> Sheet2!B2)),(Sheet1!$D$1:$D$5000))

 

[Mindtriks]

Hi Latchmaker - This formula doens't seem to be working. I've tried to clarify what I am looking for below via an example:

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.

 

[Gerald Higgins]

Would it be an acceptable solution to include re-sorting the second table, first on ID number, then on Contract Start Date ?

 

[Mindtriks]

I'm not quite sure what you are saying, could you elaborate? I understand the sorting, but I'm not sure how that will help me here. In the actual SS which I had to alter there are around 700 rows on one sheet and 5000 on another. I need to certain values to be pulled into columns (the values at the time of the negotiated contract rate, sheet 2) and then have them inserted into sheet 1. I then perform a list of functions on the given values.

 

[D_zee315]

Hello Mindtriks,

I may be a little confused on what you're trying to achieve but it may seem that an INDEX MATCH array formula would work.
The index match works similar to the vlookup formula, but has a lot more versatility. Please change the sheet name and column references according to your workbook.

=INDEX(Sheet2!D:D,MATCH(1,(Sheet1!A2=Sheet2!A:A)*(Sheet1!B2=Sheet2!B:B),0))

Remember, array formulas have to be entered by using "CTRL+SHIFT+ENTER" instead of just enter when typing in the formula. (two curly braces should appear around the formula when looking at the formula bar)

"Sheet2!D:D" would be the result that would populate,"Sheet1!A2" is the ID number on the first sheet that you're looking up on "Sheet2!A:A" and "Sheet1!B2" is the date that you would be looking on on "Sheet2!B:B".

 

[Mindtriks]

Hi D_zee315,

The formula seems close to what I need, but the values on Sheet 1 in column B are an exact date. On Sheet 2 there is a date range starting in column B and ending in column C. For example, the date could be 1/1/15 on Sheet 1, then on Sheet 2 I need to look at the start date (column B) and end date (column C) and then need to MATCH the Fixed Date with the appropriate date range from columns B and C and then pull the values in the other columns that correspond with that date. Does that make any more sense?

 

[D_zee315]

Hello Mindtriks,

I see, so if you want the data pull based on if the start date from sheet 1 falls between the contract start date and contract end date on sheet 2, then this should work:

=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))

Same deal as before, enter it using CTRL+SHIFT+ENTER.

Let me know if this works out for you or if I missed something.

 

[Mindtriks]

This was perfect and worked great. Thanks again for all the help. Also, why do you use the Asterisk (before the IF statement)?

 

[D_zee315]

The Asterisks are used as a multiplication or an "AND" (however you want to look at it). I used it with the Match formula for the multiple criteria factor you were looking for.

Example, this is what the single criteria for the ID number would be so you can see the difference:

=INDEX(Sheet2!D:D,MATCH(Sheet1!A2,Sheet2!A:A,0))

This formula would NOT need to use CTRL+SHIFT+ENTER, but it will only return the value from the first time the ID number comes up on Sheet2!A:A.