Calc - How to build an array reference as matrix reference in vlookup [SOLVED]

[mgl]

Hello,
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

[mgl]

Sorry, CTXT(n;d;m) is a French function to convert a number n into a text, with d decimals and some separation of thousands if set to 0 or omitted. I do not knwo the English equivalent.

[JohnSUN]

English equivalent is NUMBERVALUE()

The formula you are looking for can be much more complicated (or vice versa - simpler). Hard to say. Can you make a test spreadsheet that will simulate your real data? I mean two sheets PREV and YEAR with some data that can give a verifiable result?

[mgl]

Please find in here Zaclys cloud a set of data that may illustrate more clearly this issue. Sorry for the French formula : RECHERCHEV = VLOOKUP, ANNEE for YEAR and CTXT for NUMBERVALUE

[JohnSUN]

Don’t worry about this - Calc is very smart. When I opened the book it looked exactly like VLOOKUP(), YEAR() and FIXED() (I made a mistake, CTXT is FIXED, not NUMBERVALUE :-))

Now let’s see what the necessary formula should be…

[JohnSUN]

Please check this formula. I do not know how INDEX() and MATCH() will be in French, but I am sure that Calc will translate function names correctly

Test_index_match.ods

Update. I don’t like the previous solution. It may not contain errors, but this is not guaranteed.

I propose another version of the formula. First, the components of this formula:

SUMPRODUCT((YEAR(PREV.$B$8:$B$8074)=A6); (PREV.$A$8:$A$8074<=B6))

Each of the two operators will return 0 or 1 (FALSE or TRUE), and SUMPRODUCT() will simply count the number of cells where both comparisons were TRUE

If no suitable line was found on the PREV sheet (0 or FALSE), then return the indicated year on January 31.

DATE(A6;12;31)

Otherwise, you need to find the maximum date where the amount is less than or equal to the desired:

MAX((YEAR(PREV.$B$8:$B$8074)=A6)*(PREV.$A$8:$A$8074<=B6)*PREV.$B$8:$B$8074)

In order for this MAX () to be executed, the formula must be entered as the formula of the array (Ctrl+Shift+Enter).

Or we can put in the another function SUMPRODUCT()

The final formula would be:

    =IF(SUMPRODUCT((YEAR(PREV.$B$8:$B$8074)=A6);(PREV.$A$8:$A$8074<=B6)); 
   SUMPRODUCT(MAX((YEAR(PREV.$B$8:$B$8074)=A6)*(PREV.$A$8:$A$8074<=B6)*PREV.$B$8:$B$8074));DATE(A6;12;31))

Test spreadsheet - Test_sumproduct.ods

[JohnSUN]

By the way, I’m not sure which of the dates you are looking for. The latter, when the amount has not yet been reached (the formula works exactly same right now). Or the next day? For example, for 13,589
it should be 20 mai 2020 or 21 mai 2020 (coming soon!)

[mgl]

Many thanks for this input : it gives me a track to find what I need. However, MATCH provides a relative position, starting form the top left corner of the matrix. In the present case, for the year 2021, if I set the real to 600, the result should be 2021 Jan. 15th

I tried to offset the line number in the INDEX function by adding MATCH(DATE(A8;1;1);PREV.$B$5:$B$8074;1)-1 to the actual MATCH result but without success.

To enlighten this odd behaviour, I removed the IFERROR function.

Any other suggestion ?

[mgl]

By the way, it took me one hour to try to understand the structure of the core formula in D7, by example : INDEX(PREV.$B$5:$B$8074;MATCH(B7;IF(YEAR(PREV.$B$5:$B$8074)=A7;PREV.$A$5:$A$8074;"");1))

I never met before any matrix definition by mean of an IF function, in order to focus on a part of a great matrix, like a SQL clause would filter out the non-matching data records with WHERE PREV.$B = A7

I don’t know if the English documentation details this capability but at least, the French one should be enlarged with such a powerful feature

[JohnSUN]

About -1 to MATCH(). We can tut to INDEX() other first cell, not PREV.$B$5:… but INDEX(PREV.$B$6:… Or PREV.$B$4? I don’t know, need tests…

Today I accidentally (very on time :-)) came across a film that I made three years ago, I already managed to forget about it. Perhaps this will serve as an impetus to another formula (a description of the problem to be solved - in the link in the video description)

[JohnSUN]

The function IF(YEAR(PREV.$B$5:$B$8074)=A7;PREV.$A$5:$A$8074;"") will build such an array { | |...(more 400 empty values - for 2018 and 2019)...| |47|88|128|...(all 366 values of 2020)...||39088|39120|39152| | |...(empty cells to the end of list)...}

Parameter 1 (the last in the MATCH() function) says that the data is sorted in ascending order. And this is not so - there are empty values ​​both at the beginning and at the end of the array. So situations are possible when MATCH() will return the wrong result. We need to look for another way to solve the problem. Probably using OFFSET()

[mgl]

Hello John,
Either form of the new solution works perfectly as expected.
Many thanks for your availability and your pedagogy.
Cheers Michel

[mariosv]

Please, if the answer solves the question click .