If this cell equals a date in that column and this other column says "direct" then.....

[number1pita]

Hello,

I'm trying to get help on a formula I am trying to create. I want my cell to be if A2 = the date in the column on Sheet3 cells A2-A25 and cells L6-L49 equal the word "Direct" then my cell would fill in the date that is in cells B2-B25 on Sheet 3. If cells L6-L49 equal the word "Accelerated" then my cell would fill in the date that is in the cells I2-I25 on Sheet3. This is the formula I am using, and it isn't working and I can't figure out why. Any help would be greatly appreciated!


=IF(AND(A2=Sheet3!A2:A25, L6:L49="Direct"), Sheet3!B2:B25, Sheet3!I2:I25)</SPAN>


I can't post an image or upload a file, not sure if this helps at all, but the first part is the "New Hires" tab and the second part is "Sheet3"

New Hire Report</SPAN> 11/17/2014</SPAN> School Coordinator: Feb. 10-Ashley Alexander or Feb. 24-Amanda Harris</SPAN> Totals:</SPAN> Central:</SPAN> 1</SPAN> Illinois:</SPAN> 2</SPAN> Southern:</SPAN> 2</SPAN> Western:</SPAN> 1</SPAN> Total All:</SPAN> 6</SPAN> Rep #</SPAN> Rep Name</SPAN> Region/District</SPAN> State</SPAN> Agency </SPAN> Agency Manager</SPAN> P/C & L/H</SPAN> 6/63</SPAN> Path</SPAN> School 1 Date</SPAN> ABC/ADC</SPAN> 23077</SPAN> Ryan</SPAN> Skarperud</SPAN> CN</SPAN> 01</SPAN> ND</SPAN> Northern Lights</SPAN> Phil</SPAN> Haug</SPAN> Angie Hayden</SPAN> 23143</SPAN> Jeff</SPAN> Bryant</SPAN> IL</SPAN> 02</SPAN> IL</SPAN> Mchenry</SPAN> Kevin</SPAN> Glogovsky</SPAN> Bruce Minus</SPAN> 23286</SPAN> Julie</SPAN> Schutz</SPAN> IL</SPAN> 04</SPAN> IL</SPAN> Greene</SPAN> J R</SPAN> Earley</SPAN> Steve Harvey</SPAN> 23436</SPAN> Pierce</SPAN> Markowitz</SPAN> SR</SPAN> 01</SPAN> GA</SPAN> Southeast Ga</SPAN> David</SPAN> Pence</SPAN> Vicki Rickicki</SPAN> 23444</SPAN> Deborah</SPAN> Brown</SPAN> WS</SPAN> 02</SPAN> WA</SPAN> Vancouver</SPAN> Kirby</SPAN> McClanahan</SPAN> Christine Maguire</SPAN> 23485</SPAN> Godwin</SPAN> Akiti</SPAN> SR</SPAN> 01</SPAN> GA</SPAN> Metro Atl East</SPAN> Norma</SPAN> West</SPAN> Delores Smith</SPAN> <TBODY> </TBODY>

1/13/2014</SPAN> 1/27/2014</SPAN> 11/10/2014</SPAN> 1/23/2014</SPAN> 4/13/2015</SPAN> 1/13/2014</SPAN> 1/20/2014</SPAN> 1/27/2014</SPAN> 2/10/2014</SPAN> 1/27/2014</SPAN> 2/10/2014</SPAN> 11/10/2014</SPAN> 2/6/2014</SPAN> 4/27/2015</SPAN> 1/27/2014</SPAN> 2/3/2014</SPAN> 2/10/2014</SPAN> 2/24/2014</SPAN> 2/10/2014</SPAN> 2/24/2014</SPAN> 11/10/2014</SPAN> 2/20/2014</SPAN> 5/10/2015</SPAN> 2/10/2014</SPAN> 2/17/2014</SPAN> 2/24/2014</SPAN> 3/10/2014</SPAN> 2/24/2014</SPAN> 3/10/2014</SPAN> 12/8/2014</SPAN> 3/6/2014</SPAN> 5/24/2015</SPAN> 2/24/2014</SPAN> 3/3/2014</SPAN> 3/10/2014</SPAN> 3/24/2014</SPAN> 3/10/2014</SPAN> 3/24/2014</SPAN> 12/8/2014</SPAN> 3/20/2014</SPAN> 6/10/2015</SPAN> 3/10/2014</SPAN> 3/17/2014</SPAN> 3/24/2014</SPAN> 4/7/2014</SPAN> 3/24/2014</SPAN> 4/7/2014</SPAN> 12/8/2014</SPAN> 4/3/2014</SPAN> 6/24/2015</SPAN> 3/24/2014</SPAN> 3/31/2014</SPAN> 4/14/2014</SPAN> 4/28/2014</SPAN> 4/7/2014</SPAN> 4/28/2014</SPAN> TBD</SPAN> 4/17/2014</SPAN> 7/7/2015</SPAN> 4/7/2014</SPAN> 4/14/2014</SPAN> 4/21/2014</SPAN> 5/5/2014</SPAN> 4/21/2014</SPAN> 5/5/2014</SPAN> TBD</SPAN> 5/1/2014</SPAN> 7/21/2015</SPAN> 4/21/2014</SPAN> 4/28/2014</SPAN> 5/19/2014</SPAN> 6/2/2014</SPAN> 5/5/2014</SPAN> 6/2/2014</SPAN> TBD</SPAN> 5/15/2014</SPAN> 8/5/2015</SPAN> 5/5/2014</SPAN> 5/12/2014</SPAN> 5/26/2014</SPAN> 6/9/2014</SPAN> 5/19/2014</SPAN> 6/9/2014</SPAN> TBD</SPAN> 5/29/2014</SPAN> 8/19/2015</SPAN> 5/19/2014</SPAN> 5/26/2014</SPAN> 6/2/2014</SPAN> 6/16/2014</SPAN> 6/2/2014</SPAN> 6/16/2014</SPAN> TBD</SPAN> 6/12/2014</SPAN> 9/2/2015</SPAN> 6/2/2014</SPAN> 6/9/2014</SPAN> 6/23/2014</SPAN> 7/7/2014</SPAN> 6/16/2014</SPAN> 7/7/2014</SPAN> TBD</SPAN> 6/26/2014</SPAN> 9/16/2015</SPAN> 6/16/2014</SPAN> 6/23/2014</SPAN> 6/30/2014</SPAN> 7/14/2014</SPAN> 6/30/2014</SPAN> 7/14/2014</SPAN> TBD</SPAN> 7/10/2014</SPAN> 9/30/2015</SPAN> 6/30/2014</SPAN> 7/7/2014</SPAN> 7/14/2014</SPAN> 7/28/2014</SPAN> 7/14/2014</SPAN> 7/28/2014</SPAN> TBD</SPAN> 7/24/2014</SPAN> 10/14/2015</SPAN> 7/14/2014</SPAN> 7/21/2014</SPAN> 7/28/2014</SPAN> 8/11/2014</SPAN> 7/28/2014</SPAN> 8/11/2014</SPAN> TBD</SPAN> 8/7/2014</SPAN> 10/28/2015</SPAN> 7/28/2014</SPAN> 8/4/2014</SPAN> 8/25/2014</SPAN> 9/8/2014</SPAN> 8/11/2014</SPAN> 9/8/2014</SPAN> TBD</SPAN> 8/21/2014</SPAN> 11/11/2015</SPAN> 8/11/2014</SPAN> 8/18/2014</SPAN> 9/1/2014</SPAN> 9/15/2014</SPAN> 8/25/2014</SPAN> 9/15/2014</SPAN> TBD</SPAN> 9/4/2014</SPAN> 11/25/2015</SPAN> 8/25/2014</SPAN> 9/1/2014</SPAN> 9/8/2014</SPAN> 9/22/2014</SPAN> 9/8/2014</SPAN> 9/22/2014</SPAN> TBD</SPAN> 9/18/2014</SPAN> 12/8/2015</SPAN> 9/8/2014</SPAN> 9/15/2014</SPAN> 9/22/2014</SPAN> 10/6/2014</SPAN> 9/22/2014</SPAN> 10/6/2014</SPAN> TBD</SPAN> 10/2/2014</SPAN> 12/22/2015</SPAN> 9/22/2014</SPAN> 9/29/2014</SPAN> 10/6/2014</SPAN> 10/20/2014</SPAN> 10/6/2014</SPAN> 10/20/2014</SPAN> TBD</SPAN> 10/16/2014</SPAN> 1/6/2016</SPAN> 10/6/2014</SPAN> 10/13/2014</SPAN> 10/20/2014</SPAN> 11/3/2014</SPAN> 10/20/2014</SPAN> 11/3/2014</SPAN> TBD</SPAN> 10/30/2014</SPAN> 1/20/2016</SPAN> 10/20/2014</SPAN> 10/27/2014</SPAN> 11/3/2014</SPAN> 11/17/2014</SPAN> 11/3/2014</SPAN> 11/17/2014</SPAN> TBD</SPAN> 11/13/2014</SPAN> 2/3/2016</SPAN> 11/3/2014</SPAN> 11/10/2014</SPAN> 11/24/2014</SPAN> 12/8/2014</SPAN> 11/17/2014</SPAN> 12/8/2014</SPAN> TBD</SPAN> 11/27/2014</SPAN> 2/17/2016</SPAN> 11/17/2014</SPAN> 11/24/2014</SPAN> 12/1/2014</SPAN> 12/15/2014</SPAN> 12/1/2014</SPAN> 12/15/2014</SPAN> TBD</SPAN> 12/11/2014</SPAN> 3/1/2016</SPAN> 12/1/2014</SPAN> 12/8/2014</SPAN> TBD</SPAN> TBD</SPAN> <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[number1pita]

So essentially I want the School 1 Date column to be if A2 on New Hire tab = a date in column A of Sheet 3, and if the Path column says "Direct" then return the value from the row from column B on Sheet3 that lines up with the same date that is equal to A2 if that makes any sense at all.