VLOOKUP returning #NAME?

[redimp]

Tis me again

The following formula:
=IFERROR(VLOOKUP($A4,'G:\Plan_Asset\School Organisation\Numbers on Roll\[CSPA_196_SCH Number on Roll_Jan_2013.xls]Nursery NC Year'!$C$4:$I$9,6,FALSE),IFERROR(VLOOKUP($A4,'G:\Plan_Asset\School Organisation\Numbers on Roll\[CSPA_196_SCH Number on Roll_Jan_2013.xls]Primary NC Year'!$C$4:$S$280,13,FALSE),IFERRROR(VLOOKUP($A4,'G:\Plan_Asset\School Organisation\Numbers on Roll\[CSPA_196_SCH Number on Roll_Jan_2013.xls]Secondary NC Year'!$C$4:$S$280,12,FALSE),IFERROR(VLOOKUP($A4,'G:\Plan_Asset\School Organisation\Numbers on Roll\[CSPA_196_SCH Number on Roll_Jan_2013.xls]Special NC Year'!$C$5:$Z$25,21,FALSE),IFERROR(VLOOKUP($A4,'G:\Plan_Asset\School Organisation\Numbers on Roll\[CSPA_196_SCH Number on Roll_Jan_2013.xls]PRU NC Year'!$B$5:$S$6,15,FALSE),0)))))
Returns the correct value 60% of the time. However for the other 40% is returns #NAME?. Can anybody advise why and what to do about it please. All columns are set to General and I have checked for hidden characters.

[Pete_UK]

Presumably one of those sheets doesn't exist (or is named differently in the file), and this only shows up when you are trying to access data from that sheet. Just check the sheet names in the file for extra spaces. Use Evaluate Formula on one of the faulty cells and step through to try to identify why it is giving that error.

Hope this helps.

Pete

[redimp]

Been through it, can't find anything. Decided to rewrite it using more local data:

=IFERROR(VLOOKUP($A8,Nurseries!$A$2:$O$6,14,FALSE),IFERROR(VLOOKUP($A8,Primaries!$A$2:$O$278,14,FALSE),IFERRROR(VLOOKUP($A8,Secondaries!$A$2:$O$55,14,FALSE),IFERROR(VLOOKUP($A8,Specials!$A$2:$P$22,15,FALSE),IFERROR(VLOOKUP($A8,PRUs!$A$2:$O$2,14,FALSE),0)))))

with the same result. It seems to stall at the point:

IFERRROR(VLOOKUP($A8,Secondaries!$A$2:$O$55,14,FALSE)

as it returns all results for enquiries up to that.

To put it politely, it is doing my head in and it is probably something very simple!

[Pepe Le Mokko]

If you replace VLOOKUP($A8,Secondaries!$A$2:$O$55,14,FALSE) with

Please Login or Register  to view this content.

do you still get the error?

[redimp]

Yes Thanks

[redimp]

And it is that section because if I take it out enquiries to the other sheets all work. Just cannot see anything wrong with that enquirie and the sheet exists!

[Pepe Le Mokko]

Have you tried formula auditing to find dependents?

[Pete_UK]

Well, maybe it's time to post a sample workbook - the FAQ describes how to.

Pete

[redimp]

Stripped down to the bare bones of the one that does not work and one that does.
Sample VLOOKUP Book.xlsx

[Pete_UK]

Ah! Difficult to spot, but you have IFERRROR, i.e. 3 Rs in the middle.

Hope this helps.

Pete

[redimp]

To put it politely, it is doing my head in and it is probably something very simple!

I thought it would be something like that - these things really can be frustrating at times. Thanks for seeing the twig in the forest for me.