IF and VLOOKUP - limited number of characters?

[Craig]

Does the VLOOKUP method and the IF function have a limited number of characters within their parameters/argument that they can handle?

I've currently got a problem with these as follows:


This first line functions correctly:

=IF($D50=0,0,VLOOKUP($G76,'[SAC tonnages v2.xls]totals'!$B$3:'[SAC tonnages v2.xls]totals'!$D$35,3,FALSE))


but, this line returns #REF:

=IF($D50=0,0,VLOOKUP($G76,'E:\CFR\000100\MajorP Projects (Start)\Iceland -Brg42 5172\PolygonModelling\MODELLING DATA\Iceland_Compaction\[SAC tonnages v2.xls]totals'!$B$3:'E:\CFR\000100\MajorPProjects (Start)\Iceland -Brg42 5172\PolygonModelling\MODELLING DATA\Iceland_Compaction\[SAC tonnages v2.xls]totals'!$D$35,3,FALSE))

NOTE: Both lines refer to the same range within the same (separate) Excel sheet, but in the first line, the [SAC tonnages v2.xls] worksheet is open on the same PC. In the second line, the [SAC tonnages.xls] sheet is closed and Excel automatically inserts the full directory path to the [SAC tonnages.xls].


Essentially, I'd like to know why the 2nd line doesn't work but the first one does...

(My thoughts are that the IF function and VLOOKUP method don't like the large number of parameters..and/or what about the white spaces in the file path?)

 

[Frank Kabel]

Hi Craig,
try the following:
=IF($D50=0,0,VLOOKUP($G76,'[SAC tonnages
v2.xls]totals'!$B$3:$D$35,3,FALSE)) ´
HTH
Frank

 

[Craig]

Thanks very much Frank - works perfectly

(Maybe if I'd spent as much time looking properly at this from a logical point of view instead of typing this question I may have worked this out!)

Thanks again

----- Frank Kabel wrote: ----

Hi Craig
try the following
=IF($D50=0,0,VLOOKUP($G76,'[SAC tonnage
v2.xls]totals'!$B$3:$D$35,3,FALSE)) Â
HT
Fran


Craig wrote

Does the VLOOKUP method and the IF function have a limited number o
characters within their parameters/argument that they can handle

I've currently got a problem with these as follows

This first line functions correctly

=IF($D50=0,0,VLOOKUP($G76,'[SAC tonnages v2.xls]totals'!$B$3:'[SA

tonnages v2.xls]totals'!$D$35,3,FALSE)

=IF($D50=0,0,VLOOKUP($G76,'E:\CFR\000100\MajorP Project

(Start)\Iceland -Brg42 5172\PolygonModelling\MODELLIN
DATA\Iceland_Compaction\[SAC tonnage
v2.xls]totals'!$B$3:'E:\CFR\000100\MajorPProjects (Start)\Icelan
-Brg42 5172\PolygonModelling\MODELLIN
DATA\Iceland_Compaction\[SAC tonnages v2.xls]totals'!$D$35,3,FALSE)

NOTE: Both lines refer to the same range within the same (separate

Excel sheet, but in the first line, the [SAC tonnages v2.xls
worksheet is open on the same PC. In the second line, the [SA
tonnages.xls] sheet is closed and Excel automatically inserts th
full directory path to the [SAC tonnages.xls]

first one does..
(My thoughts are that the IF function and VLOOKUP method don't lik

the large number of parameters..and/or what about the white spaces i
the file path?

 

[Frank Kabel]

Thanks very much Frank - works perfectly!

(Maybe if I'd spent as much time looking properly at this from a
logical point of view instead of typing this question I may have
worked this out!).

you're welcome
Frank