Address function to create range

Hi

I am trying to match a value within a dynamic range i.e.

match(a1, ADDRESS(MATCH(C127,dates,0)+2,5):ADDRESS(MATCH(C127,dates,0)+54,5),0)

to see whether a value exists. The range will change depending on the value returned in cell C127. I am effectively trying to find out whether a date appears is contained within that year.

Could you please help!
Thanks
James

Hello
The ADDRESS function only returns a text value and needs to be enclosed with INDIRECT to convert it to a range that Excel can recognize. You also need to concatenate the two ADDRESS functions with (&":"&), as in below:

Formula:

=MATCH(A1, INDIRECT(ADDRESS(MATCH(C127,Dates,0)+2,5)&":"&ADDRESS(MATCH(C127,Dates,0)+54,5)),0)

DBY