Hello everyone,
I currently follow spreadsheet courses, and I’m stuck on the following problem (file included) :
On the Q5 sheet, I need to extend a cell with a VLOOKUP formula. I’m unable to, as I can’t find a way to write a “variable” index value to select the right column automatically while extending.
Any clue on what to do ? Thanks in advance for your answers.
OS : Linux Mint 22.1 (Cinnamon)
LO : 25.2.1.2
P4 Exos.ods (28.6 KB)
Are you explicitly forced to use VLOOKUP()?
If so, you need to find a valid expression (subformula) for the index of the column to take the values from, which is the third argument.
If equal sequences of years for both sheets are assured you can get it with the help of COLUMN() expressions - one with relative, one with absolute column address. OK?
Personally I would prefer to use the INDEX() function, and to find the indices with the help of MATCH(). This would make things indepentent of assurances about the sequences of years, and would make the vertical matching which is implicit with VLOOKUP() explicit now and possibly clearer.
I am
I will try the COLUMN formula tomorrow, and if it works I’ll mark your answer as the solution
EDIT : the COLUMN function worked, thanks very much