Hyperlink Cell stopped working after changing format

[SteveNL86]

Dear reader,

I'm trying to hyperlink a cell with a date in it, to jump to that date in another sheet. I've had it working but I changed the format from the cell from JJJJ/MM/DD to DD-MM-JJJJ, and now it stopped working.

I'm using this formula in the cell:

=HYPERLINK("#"&CELL("address";INDEX(Rooster2021!B2:B3885;MATCH(TEXT(Registratie!B13;"dd-mm-jjjj");Rooster2021!B2:B3885;2)));"Klik voor datum in rooster")

I've tried changing the format in the formula aswell as in the other sheet with the dates multiple times but it doesn't seem to want to work. I'm not sure what I'm doing wrong.

Any help is much appreciated.
Kind regards.

 

[Toadstool]

Hi SteveNL86,

You MATCH has a match type of 2 but that is not an option. I believe a value of 2 is treated as a 1 so "match_type = 1 or omitted: MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order" which I don't think is what you want.

If the format of the dates in Rooster2021! is text then changing the match_type to 0 will find an exact match.

If the format of the dates in Rooster2021! is numeric date format then try changing the MATCH to:
MATCH(DATE(YEAR((Registratie!B13),MONTH((Registratie!B13),DAY((Registratie!B13)),Rooster2021!B2:B3885,0)

 

[SteveNL86]

Thank you Toadstool, you got me in the right direction.

I ended up changing it to:

=HYPERLINK("#"&CELL("address";INDEX(Rooster2021!B2:B5000;MATCH(DATE(YEAR((Registratie!B13));MONTH((Registratie!B13));DAY((Registratie!B13)));Rooster2021!B2:B5000;2)));"Klik voor datum in rooster")

The reason I was using 2 was to get the last value in the list so you don't have to scroll when looking for all the dates.

Thanks for your help!