May 17 2019 02:07 AM
Hello,
I work in a school and am trying to set up a spreadsheet to track pupil progress for our disadvantaged pupils. The spreadsheet uses various lookups and then lookup tables to retrieve data from a system extract, and to then compare progress against expectations.
I have however come across a problem with a #VALUE error being returned that, despite looking at it for ages, I just don't understand.
Would anyone mind taking a look at the attached anonymised template? Please see column "O" of the "Progress" tab. This formula is copied from columns F,G,I,J,L,M, however for some reason the nested VLOOKUP stops working at col_index_num 17 (whereas it works when the col_index_num is 16 or less). The lookup table_array contains more than 17 columns, so I just don't understand what is going wrong.
Any help would be very much appreciated!
May 17 2019 02:27 AM
@A_Sheldon , nested VLOOKUP
VLOOKUP($D3,YEAR,17,FALSE)
returns zero, and on upper level you use 0 as column number, as result #VALUE. It shall be some number from 1 to number of your columns.
May 17 2019 02:48 AM
@Sergei Baklan thank you for looking, sorry but what do you mean by 'upper level you use 0 as column number'?
May 17 2019 02:55 AM
@A_Sheldon , another part of your formula is
VLOOKUP($A3,DATA,VLOOKUP($D3,YEAR,17,FALSE),FALSE)
which will be translated to
VLOOKUP($A3,DATA,0,FALSE)
with previous result
May 17 2019 02:56 AM
I@Sergei Baklan I see, thank you very much!
May 17 2019 04:09 AM
@A_Sheldon , you are welcome