Home

Help for school with #VALUE error on nested VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-580922%22%20slang%3D%22en-US%22%3EHelp%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-580922%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20in%20a%20school%20and%20am%20trying%20to%20set%20up%20a%20spreadsheet%20to%20track%20pupil%20progress%20for%20our%20disadvantaged%20pupils.%26nbsp%3B%20The%20spreadsheet%20uses%20various%20lookups%20and%20then%20lookup%20tables%20to%20retrieve%20data%20from%20a%20system%20extract%2C%20and%20to%20then%20compare%20progress%20against%20expectations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20however%20come%20across%20a%20problem%20with%20a%20%23VALUE%20error%20being%20returned%20that%2C%20despite%20looking%20at%20it%20for%20ages%2C%20I%20just%20don't%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20anyone%20mind%20taking%20a%20look%20at%20the%20attached%20anonymised%20template%3F%26nbsp%3B%20Please%20see%20column%20%22O%22%20of%20the%20%22Progress%22%20tab.%26nbsp%3B%20This%20formula%20is%20copied%20from%20columns%20F%2CG%2CI%2CJ%2CL%2CM%2C%20however%20for%20some%20reason%20the%20nested%20VLOOKUP%20stops%20working%20at%20col_index_num%2017%20(whereas%20it%20works%20when%20the%20col_index_num%20is%2016%20or%20less).%26nbsp%3B%20The%20lookup%20table_array%20contains%20more%20than%2017%20columns%2C%20so%20I%20just%20don't%20understand%20what%20is%20going%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20very%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-580922%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-580969%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-580969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343757%22%20target%3D%22_blank%22%3E%40A_Sheldon%3C%2FA%3E%26nbsp%3B%2C%20nested%20VLOOKUP%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EVLOOKUP(%24D3%2CYEAR%2C17%2CFALSE)%3C%2FPRE%3E%0A%3CP%3Ereturns%20zero%2C%20and%20on%20upper%20level%20you%20use%200%20as%20column%20number%2C%20as%20result%20%23VALUE.%20It%20shall%20be%20some%20number%20from%201%20to%20number%20of%20your%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581008%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20looking%2C%20sorry%20but%20what%20do%20you%20mean%20by%20'%3CSPAN%3Eupper%20level%20you%20use%200%20as%20column%20number'%3F%3C%2FSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581028%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343757%22%20target%3D%22_blank%22%3E%40A_Sheldon%3C%2FA%3E%26nbsp%3B%2C%20another%20part%20of%20your%20formula%20is%3C%2FP%3E%0A%3CPRE%3EVLOOKUP(%24A3%2CDATA%2CVLOOKUP(%24D3%2CYEAR%2C17%2CFALSE)%2CFALSE)%3C%2FPRE%3E%0A%3CP%3Ewhich%20will%20be%20translated%20to%3C%2FP%3E%0A%3CPRE%3EVLOOKUP(%24A3%2CDATA%2C0%2CFALSE)%3C%2FPRE%3E%0A%3CP%3Ewith%20previous%20result%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581038%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581038%22%20slang%3D%22en-US%22%3E%3CP%3EI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20see%2C%20thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581245%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20for%20school%20with%20%23VALUE%20error%20on%20nested%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343757%22%20target%3D%22_blank%22%3E%40A_Sheldon%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
A_Sheldon
New Contributor

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!

 

 

5 Replies

@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.

 

@Sergei Baklan thank you for looking, sorry but what do you mean by 'upper level you use 0 as column number'?  

@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

I@Sergei Baklan I see, thank you very much!