• 461K Members
• 5,618 Online
• 559K Conversations

New Contributor

# Help for school with #VALUE error on nested VLOOKUP

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

# Re: Help for school with #VALUE error on nested VLOOKUP

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

# Re: Help for school with #VALUE error on nested VLOOKUP

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

# Re: Help for school with #VALUE error on nested VLOOKUP

@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

# Re: Help for school with #VALUE error on nested VLOOKUP

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

# Re: Help for school with #VALUE error on nested VLOOKUP

@A_Sheldon , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies