Why does this work for a Name definition of a range?

=Sheet1!$A$3:INDEX(DataforCharts!$A:$A,MATCH(9.999 99E+307,DataforCharts!$A:$A))

It works but I can't figure out why. The INDEX returns the value of the
last cell with data in it, however if I just replace all of that with the
value it does not work anymore. For example this does NOT work.

=Sheet1!$A$3:40857