I try to get the date corresponding to a cumulative data in set of 20 years. Dates are ordered/classified. Cumulative data are reset to 0 on each January 1st. Cumulative data are in the first column and dates are in the second column.
So, in fact, the lookup should be done in a part of the whole set of the 20 years : the set corresponding to the actual year. And I don’t want to write the reference of this range manually but would like to have it automatically.
So in the formula VLOOKUP(actual cumul from start of the year;year matrix;2;1), I would like to have the year matrix definition build from a 2 column table giving the lines of the first date and of the last date of each year.
If possible, the matrix is in another tab of the file : $PREV tab, when the search criterion and the result are in $YEAR tab.
I am able to build the range reference like ="$PREV.AO"&CTXT(AU9;0;1)&":AP"&CTXT(AV9;0;1) to get something like $PREV.AO404:AP769. But I do not manage to “insert” this in result in the VLOOKUP formula.
Direct VLOOKUP(search criterion; “$PREV.AO”&CTXT(AU9;0;1)&":AP"&CTXT(AV9;0;1);2;0) provide ERR 502.
Using INDIRECT("$PREV.AO"&CTXT(AU9;0;1)&":AP"&CTXT(AV9;0;1);1) provide #REF
Thanks in advance for your help