Dragging a formula produces #Ref error

[royhern]

Hey guys i tried to drag this formula top to buttom but i get #ref error

=IF(ISERROR((VLOOKUP($C11,Data!1:65536,4))),"",(VLOOKUP($C11,Data!1:65536,4)))

=IF(ISERROR((VLOOKUP($C11,Data!#REF!,4))),"",(VLOOKUP($C11,Data!#REF!,4)))

and i changed it to :

=IF(ISERROR(VLOOKUP($C11,Data,4,FALSE)),"",VLOOKUP($C11,Data,4,FALSE))

But i get a blank cell?

Any ideas...?

 

[Yogi Anand]

Hi royhern:

I don't know the context or the formulas you are using -- however

in your formulation, the expression ... Data!1:65536, the arguments 1 and 65536 are both relative, so when you drag it down it tends to become Data!2:65537 -- and since the maximum row number is only 65536, you are getting the #REF! error.

So, look at the possibility of using ... Data!1:$65536 -- when you drag it down, it will result in ... Data!2:$65536

I hope this helps!

 

[just_jon]

Hey guys i tried to drag this formula top to buttom but i get #ref error

=IF(ISERROR((VLOOKUP($C11,Data!1:65536,4))),"",(VLOOKUP($C11,Data!1:65536,4)))

=IF(ISERROR((VLOOKUP($C11,Data!#REF!,4))),"",(VLOOKUP($C11,Data!#REF!,4)))

and i changed it to :

=IF(ISERROR(VLOOKUP($C11,Data,4,FALSE)),"",VLOOKUP($C11,Data,4,FALSE))

But i get a blank cell?

Any ideas...?

A couple of things -- first, using the whole sheet as your lookup array [1:65536] is a bad idea; it's also why you get a REF error. When you drag it, 1:65536 changes to 2:65537, etc -- definitely NOT good. More clearly define your ranges for the vlookup, and then anchor your references [those you wish not to change as the formula moves] with $ as in $A$1:$D$100.

Do you have a named range called Data? If so, how is it defined? My guess is no, and the reason you're getting a blank on that last formula is that, as you've not given vlookup a range but only a sheet name, it ISERROR, and is doing exactly what you have told it do do, return "".

Use of ISERROR is highly discouraged. See -- http://www.mrexcel.com/board2/viewtopic.php?t=62102

 

[royhern]

thanks got it...i forgot to defined the range..

 

[Brian from Maui]

Hey guys i tried to drag this formula top to buttom but i get #ref error

=IF(ISERROR((VLOOKUP($C11,Data!1:65536,4))),"",(VLOOKUP($C11,Data!1:65536,4)))

=IF(ISERROR((VLOOKUP($C11,Data!#REF!,4))),"",(VLOOKUP($C11,Data!#REF!,4)))

and i changed it to :

=IF(ISERROR(VLOOKUP($C11,Data,4,FALSE)),"",VLOOKUP($C11,Data,4,FALSE))

But i get a blank cell?

Any ideas...?

On using ISERROR,

http://216.92.17.166/board2/viewtopic.php?t=62102