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

number1pita

Board Regular
Joined
Oct 8, 2013
Messages
51
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>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

number1pita

Board Regular
Joined
Oct 8, 2013
Messages
51
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top