VLOOKUP Worksheet limit?

[SteveWebb]

Hi and thank you in advance for looking at my problem,

Is there a limit to the number of worksheets the VLookup function can access?

I have 'Data Table', A1 - AA6000
Headings include Ref number - Date/Time - Address etc (These contain some personal data so have not been able to post an example).

Worksheets range from LI 1 through to LI 23 all with selected headings taken from the DataTable.
The Vlookup function will look for the LI match:-
Using =IF(ISNA(VLOOKUP(A2,'LI 1'!A1:J3,16,FALSE)),VLOOKUP(A2,'LI 2'!A1:K40,16,FALSE),VLOOKUP(A2,'LI 3'!A1:P196,16,FALSE)) with CSE.
Column 16 contains the data for comparison, I get to LI 4 and get an error message.

Any help much appreciated

Kind Regards
Steve

 

[VoG]

Hi. Try VlookAllSheets http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[SteveWebb]

Hi Thanks Peter for the reply, will look at that.

Many thanks
Steve

 

[SteveWebb]

Hi Peter,
Used :-
=IFERROR(VLOOKUP(A2,'LI 8'!$A$1:$P$191,16,FALSE),IFERROR(VLOOKUP(A2,'LI 14'!$A$1:$N$120,14,FALSE),IFERROR(VLOOKUP(A2,'LI 15'!$A$1:$N$135,14,FALSE),IFERROR(VLOOKUP(A2,'LI 16'!$A$1:$L$1183,12,FALSE),IFERROR(VLOOKUP(A2,'LI 22'!$A$1:$L$1386,12,FALSE),IFERROR(VLOOKUP(A2,'LI 23'!$A$1:$N$936,14,FALSE),IFERROR(VLOOKUP(A2,'LI 50'!$A$1:$O$72,15,FALSE),"Not Found")))))))

Only draw back is the max 7 nested functions. Is this excels limit without VBA/UDF?

Cheers
Steve

 

[VoG]

Yes, in Excel versions before 2007.

 

[GlennUK]

Surely that can't be Excel 2003 or earlier as the IFERROR function is being used?!?!

 

[SteveWebb]

HI Glenn,
Have gone all modern with 2010 !!, still confuses me though

 

[GlennUK]

Ah, good. Well you should be able to go deeper than 7 levels then.

 

[SteveWebb]

Go to level 8 and warning message flagged maximum number of nested had been reached?

 

[GlennUK]

Are you in compatibility mode by any chance? Or in an older version workbook?