• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Junarkar

Member
Hi,

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

NameDOB
Anil15-May-97
Vikas1-Jun-90
Manu6-Jul-95
Das9-Dec-94
Patric18-Sep-94
Shan6-Nov-93
Mary12-Mar-97
Haris1-May-87
Vinil4-Oct-95
Lisy15-Jul-90
Boby22-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
 
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,
 
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
 
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,
 
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?
 
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,
 
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
 
Last edited:
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
 
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
 
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
 
Back
Top