Index(Match issues

[NARAYANK991]

Hi Jamie ,

Having both the holidays listed in the same cell is either difficult or impossible ; I do not know which !

You can have the multiple holidays listed in successive columns , an example of which is in the uploaded file Question_1.xlsx ; with reference to the latest formulae , which you have put in column D , you can have additional formulae in columns E , F ,... depending on how many additional holidays will be present in any one week.

An example would be :

=IFERROR(INDEX($L$3:$L$12,SMALL(IF(ISNA(MATCH($M$3:$M$12,ROW(INDEX($B$3:$B$65536,$B3-2):INDEX($B$3:$B$65536,$B3+7-1-2)),0)),999,ROW($M$3:$M$12)-MIN(ROW($M$3:$M$12))+1),2)),"")

where the MIN function has been replaced by the SMALL function , and the 2 which is in RED , signifies the second holiday in the same week ; if there will be more than 2 such holidays in the same week , you can extend this formula to columns F , G ,... by changing the 2 to 3 , 4 ,...

Check the uploaded file.

Narayan

 

[Jamie Wagler]

@SirJB7

I was able to convert your query with cell references and I had it working on the Question file that you sent me yesterday but when I tried to apply it to my worksheet (attached) it wouldn't populate the cells with the holiday. Any suggestions?

 

[Jamie Wagler]

@ SirJB7

I think that I found the answer as to why it is not working. The first period of the weekly buck is generated with =$A$36+1-WEEKDAY(TODAY(),2) to allow for it to populate with Monday dates only. and that brings the date to a decimal point and not a whole number.

 

[SirJB7]

Hi, Jamie Wagler!

I've noticed that before, the date with time (hours minutes and seconds), but as you said you had a macro to scroll up and down I assumed you'd tweak it to return integer values (no time) if necessary. And in this case it's necessary.

In your sample file change A36 formula from:
=NOW()
to:
=INT(NOW())

Regards!

 

[Jamie Wagler]

@SirJB7

Thanks! I was able to resolve the issue with =ROUND($A$36+1-WEEKDAY(TODAY(),2),0)

 

[SirJB7]

Hi, Jamie Wagler!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!