# Thread: Populate calendar based on multiple values

1. ## Populate calendar based on multiple values

Hey guys,

I am new to this forum.

Currently I am working on a small project and I am very close to the solution.

The case is as followed:
I have three columns - ABC - DateX, Project and Hours. They get populated via other sheets, the data changes every month. I made also from each column a dynamic ranged.

From column D till column AI i have a calendar.
I use an Index function to get the hours from column C to populate the calendar based on the date and project.

However, the Index function does not work correctly, it shows the wrong values on the calendar and when change a value it changes other values in the calendar.

In another forum someone helped me by providing me the following solution:

Code:
`{=IFERROR(INDEX(hours,SMALL(IF(dateX=E\$15,IF(project=\$D16,hours)),1)),"")}`
As you can see in the attachment, the first of January it provides the wrong value in the calender.

What do I do wrong?

See attachment for more details.
Calendar_Vlookup_v0.3.xlsx  Reply With Quote

2. The problem is that the selection match returns the value

IF(date=E\$15,IF(project=\$D16),hours))

and then uses this VALUE as a row index. You need to return the row number

=IFERROR(INDEX(hours,SMALL(IF((date=E\$15)*(project=\$D16),ROW(hours)-1),1)),"")  Reply With Quote

3. ## [Solved] Originally Posted by Bob Phillips The problem is that the selection match returns the value

IF(date=E\$15,IF(project=\$D16),hours))

and then uses this VALUE as a row index. You need to return the row number

=IFERROR(INDEX(hours,SMALL(IF((date=E\$15)*(project=\$D16),ROW(hours)-1),1)),"")
Thanks Bob for our quick response!!
I am testing it like crazy and it works perfectly!!!  Reply With Quote

4. Hi Bob,

Thanks again for your help. I really enjoyed developing further the calendar.
During increasing the functionalists of the calendar, i bump up to an unseal behavior.

When I have only one instance in the holiday/sick section, the calendar behaves very strange. The moment I put the second instance, than its all fine.
I looked closely into the code, but did not manage to find the "bug". Originally Posted by oddy73 Thanks Bob for our quick response!!
I am testing it like crazy and it works perfectly!!!  Reply With Quote

5. When you have just one entry in the absence table, the IF tests within the SMALL function doesn't return an array, just a scalar value, so the function doesn't work correctly.

One way to handle it is to test specifically

=IFERROR(IF(COUNT(hours)=1,IF(AND(\$A2=J\$2,\$B2=\$I3),\$C2,""),INDEX(hours,SMALL(IF((date=J\$2)*(project=\$I3),ROW(hours)-1),1))),"")  Reply With Quote

6. the test could not help me but I found a workaround.
This works also fine for me:
Code:
`=SUMIFS(\$C:\$C,\$A:\$A,J\$2,\$B:\$B,\$I3)`
Thanks anyway for looking into my request, i am very happy that there are people that take time to help one and other  Originally Posted by Bob Phillips When you have just one entry in the absence table, the IF tests within the SMALL function doesn't return an array, just a scalar value, so the function doesn't work correctly.

One way to handle it is to test specifically

=IFERROR(IF(COUNT(hours)=1,IF(AND(\$A2=J\$2,\$B2=\$I3),\$C2,""),INDEX(hours,SMALL(IF((date=J\$2)*(project=\$I3),ROW(hours)-1),1))),"")  Reply With Quote

calendar, formula, index 