• 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.

Index Match Array For Multiple Column Search

J

jaynes

New Member
Good Morning,

I have been a chandoo lurker for quite some time and have learned a great deal from posts. Some stuff I don't understand yet, but I am working on it.

I am posting today because I have a question about using index and match to populate unique names of individuals who are taking vacation time.

The attached spreadsheet includes the VacationSched worksheet, which shows the calendars and how many people from different offices have time off. This should also include the names of the individuals who have been scheduled for time off. The number of names should equal the sum of Disney and Hanna Barbera; for example January 1, 2014 should have seven names because Disney has 5 scheduled off and Hanna Barbera has 2 scheduled off.

I'm not sure if the calculation can be completed this way, or if the data has to be reformatted. Any pointers and assistance is appreciated!
 

Attachments

  • TEMPScheduling.xlsx
    283.9 KB · Views: 12
As far as I can tell, there is an issue using index and match for multiple columns. I would like to use the index and match array across multiple columns to find a date and return the name. Does this help more?
 
Hi Jaynes ,

One of the conditions you are checking for is :

VACA[EndDate]<=$B$9

If you see the value of B9 , it is January 2, 2014 ; putting this as a filter in your table , you can see that there are only 4 names which meet this criterion.

The formula by which you are calculating 5 and 2 must be wrong.

Narayan
 
NARAYANK991, thank you for the reply. That is one of the criteria, however it is a sum of all the columns that have that date. It checks up to 14 columns to count the number of times that date occurs included in the entire vacation period, not just the end date.
 
Hello Jaynes,

Try this Array Formula in F6 & copy down. Adjust reference in other columns.

=IFERROR(UPPER(INDEX(VACA[Name],MATCH(1,(VACA[EndDate]>=B$8)*(VACA[StartDate]<=B$8)*ISNA(MATCH(VACA[Name],F$5:F5,0))*(VACA[Active]="A"),0))),"")

Jaynes, the way you have set up the calendar is very hard to change the cell reference when copy formula across & down to other days. I suggest you to something simply like,

In K2, use 4 digit year. 2014 or you can use formula =YEAR(TODAY())

C3: =DATE(YEAR(K2),1,1) & format as "mmmm" so this will show as January

C4:I4, Sun to Sat

Delete B5 formula & make it blank

C5, then copy to I5

=IF(TEXT($C3,"ddd")=C4,$C3,IF(N(B5),B5+1,""))

Then use custom format "d". So this will show date as 1,2,3....28,29,30,31

Then you can simply use Array Formula in C6, then drag to I13

=IFERROR(UPPER(INDEX(VACA[Name],MATCH(1,(VACA[EndDate]>=C$5)*(VACA[StartDate]<=C$5)*ISNA(MATCH(VACA[Name],C$5:C5,0))*(VACA[Active]="A"),0))),"")

Copy to below date & drag across
 
Good Morning, Haseeb,

Thank you for the comprehensive reply. In applying the solution, when creating a date such as year(today()), or to enter the formula today() and then calculate year in another cell, it becomes "1905". This is part of the originating issue and how it evolved as it has. I would love your solution!

So, there must be a formatting issue somewhere along the line? Any idea how to resolve that odd year thing?

Thanks!

Jan
 
Haseeb, I found the formatting issue and am working on it. I will follow up later today with additional information about the solution provided.

Thank you very much for your help!

Jan
 
I am sorry Jaynes. I made a mistake in typo. In C3 remove YEAR formula. it must be

=DATE(K2,1,1)

Firstly it was looking for YEAR(2014) which is 1905. 2014 is actually 6-Jul-1905.

My bad Jaynes....
 
Not a problem. LOL - I did figure that part out and started working with the actual formula I need... Then I had to do other things. I haven't been able to get to it since last week.
 
Haseeb, thanks for the help. I made the recommended changes and then tweaked the formula in order to focus on the calendar date, and it works!

=IFERROR(UPPER(INDEX(VACA[Name],MATCH(1,(DATE($K$2,MONTH($C$3),F$5)<=VACA[EndDate])*(DATE($K$2,MONTH($C$3),F$5)>=VACA[StartDate])*ISNA(MATCH(VACA[Name],F$5:F5,0))*(VACA[Active]="A"),0))),"")
 
Haseeb, I do have a question about this. Normally, when I use the match and refer to an array, Match(0, is normally what I use. Why use Match(1 ?
 
You must log in or register to reply here.
Back
Top