Hi Excel Experts,
I have a table consists of the followings:-
Col A Col B
2/2/2014 PH
5/2/2014 PH
21/2/2014 PH
23/2/2014 PH
25/2/2014 CL
3/3/2014 PH
6/3/2014 EV
9/3/2014 PH
12/3/2014 PH
20/3/2014 EV
22/3/2014 EV
25/3/2014 PH
28/3/2014 CL
1/1/2015 EV
5/1/2015 PH
11/1/2015 PH
12/1/2015 PH
18/1/2015 PH
19/1/2015 PH
20/1/2015 PH
23/1/2015 PH
24/1/2015 EV
25/1/2015 CL
26/1/2015 PH
Please take note that dates in Col A are values from Index Match from another worksheet).
I want to get the date which match the following criterias which will be manual entries by the user.
E.g.
C1 : 2014 (data validation list)
C2 : February (data validation list)
C3 : CL (returned value from index match from another worksheet)
So far, I fail to get the date because I don't know how to set the MATCH formula for the Month name in C2 to match the month in the range of dates in Col A. Below is what I got so far (by logic thinking):-
{=INDEX($A$11:$A$80,MATCH(1,(MONTH($A$11:$A$36)=$C$2)*("CL"=$B$11:$B$80),0))} (Note: {} is from CTRL+SHIFT+ENTER)
I'm trying to extract the month in the range of dates that meets the criteria mentioned in C2, but format for both are different. I tried using DATEVALUE or TEXT($A$11:$A$80,"mmmm") still error.
Appreciate if someone can help promptly.
Thank you in advance.
Bookmarks