Get Dates if the month of which is equal to current month

[Junarkar]

Hi,

I have a data sheet like below with Date of Birth of employees;

Name	DOB
Anil	15-May-97
Vikas	1-Jun-90
Manu	6-Jul-95
Das	9-Dec-94
Patric	18-Sep-94
Shan	6-Nov-93
Mary	12-Mar-97
Haris	1-May-87
Vinil	4-Oct-95
Lisy	15-Jul-90
Boby	22-Sep-96

In another sheet I want to get the name of employee if his birthday falls on current month;

I used below formula but it is giving only first match where I want name of all matches.
={INDEX(Emp_Name,SUMPRODUCT(MONTH(DOB)=MONTH(TODAY())),MATCH(ROW(DOB),ROW(DOB)))}

Thanks

 

[pecoflyer]

This technique is described at https://www.extendoffice.com/docume...okup-return-multiple-values-horizontally.html
Just add a condition for MONTH
OTOH, why not simply use a filter?
The latest XL version also has a FILTER function https://support.office.com/en-us/article/FILTER-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

 

[Khalid NGO]

Hi,

Please try this:

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/((MONTH(DOB)=MONTH(TODAY()))/(ROW(DOB)-1)),ROWS($1:1)),),"")

Red highlighted -1 assumed that data starts from 2nd Row, needs to be adjusted accordingly.

Regards,

 

[Junarkar]

This technique is described at https://www.extendoffice.com/docume...okup-return-multiple-values-horizontally.html
Just add a condition for MONTH
OTOH, why not simply use a filter?
The latest XL version also has a FILTER function https://support.office.com/en-us/article/FILTER-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

Thanks for sharing the link.
But I stuck at a point because how can I compare the month of dates in a range with month of current month? I can't use filter because I want this data in a dashboard.

My formula looks like this
{=INDEX(Emp_Name,SMALL(IF(Month(TODAY())=DOB,ROW(Emp_Name)-ROW(CSS!$A$2)+1),COLUMN(CSS!$B$1)))}

I know this part is wrong - Month(TODAY())=DOB.

Can you please help.

Thanks

 

[Khalid NGO]

I know this part is wrong - Month(TODAY())=DOB.

Hi,

Your DOB range contains Dates, whereas MONTH(TODAY()) will return current month number, say current month number is 5, and 5 is not equals to any of the dates mentioned in DOB range, therefore Month(TODAY())=DOB will return the array of FALSE only.

You can return TRUE / FALSE array by wrapping DOB with MONTH, like this:
MONTH(TODAY())=MONTH(DOB)

Regards,

 

[Junarkar]

Hi,

Please try this:

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/((MONTH(DOB)=MONTH(TODAY()))/(ROW(DOB)-1)),ROWS($1:1)),),"")

Red highlighted -1 assumed that data starts from 2nd Row, needs to be adjusted accordingly.

Regards,

Hi Khalid,

I tried it but it returns error. I couldn't figure out the reason for error. Below is my formula and I tried it without CSE;

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/((MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))/ROW(CSS!$G$3:$G$22)-2),ROW($1:1)),),"")

When checked

(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))/ROW(CSS!$G$3:$G$22) - (in my sheet DOB is in column "G"), Returns below result;

{0.333333333333333;0;0;0;0;0;0;0;0.0909090909090909;0;0;0;0;0;0;0;0;0;0;0} - As I said original data starts from G3:G22

And in turn Aggregate function returns {-0.6}

What I did wrong?

 

[Khalid NGO]

Hi,
Yes AGGREGATE takes care of CSE in most of the formulas.


I am not sure what did wrong, just make sure your range Emp_Name and CSS!$G$3:$G$22 are of same sizes.

You can always upload sample file with same structure of original file without any sensitive information, this will lead us to share targeted solutions.

Regards,

 

[bosco_yip]

Hi Khalid,

I tried it but it returns error. I couldn't figure out the reason for error. Below is my formula and I tried it without CSE;

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/((MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))/ROW(CSS!$G$3:$G$22)-2),ROW($1:1)),),"")

When checked

(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))/ROW(CSS!$G$3:$G$22) - (in my sheet DOB is in column "G"), Returns below result;

{0.333333333333333;0;0;0;0;0;0;0;0.0909090909090909;0;0;0;0;0;0;0;0;0;0;0} - As I said original data starts from G3:G22

And in turn Aggregate function returns {-0.6}

What I did wrong?

Try...……….

Replaced your formula by :

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))*(ROW(CSS!$G$3:$G$22)-2),ROWS($1:1)),),"")

Or,

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,ROW(CSS!$G$3:$G$22)-2/(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY())),ROWS($1:1)),),"")

Regards
Bosco

 

[Somendra Misra]

Hi,

Just a passing thought, isn't Pivot can solve the problem here very easily.

Until unless the question is about learning advanced formulas.

Regards,
Somendra

 

[Junarkar]

Try...……….

Replaced your formula by :

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY()))*(ROW(CSS!$G$3:$G$22)-2),ROWS($1:1)),),"")

Or,

=IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,ROW(CSS!$G$3:$G$22)-2/(MONTH(CSS!$G$3:$G$22)=MONTH(TODAY())),ROWS($1:1)),),"")

Regards
Bosco

Hi

The second formula done the work . Thanks alot for the solution.

Regards
Junarkar

 

[Junarkar]

Hi,

Just a passing thought, isn't Pivot can solve the problem here very easily.

Until unless the question is about learning advanced formulas.

Regards,
Somendra

Hey

Piviot can also do the work but like you said I was curious to learn how it can be done with formula.

Thanks