Highlighted
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
Highlighted

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

Highlighted

# 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'?

Highlighted

# 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

Highlighted

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

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

Highlighted

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

@A_Sheldon , you are welcome