VLOOKUP by columns

Hi guys,
I’ve been looking all over the web, but I wasn’t able to find anything sensible.

I use VLOOKUP to find from a list of hostnames their matching IP addresses and other info to build another list.
The first sheet contains in column A the hostname and the other columns with IP adresss and other data.
In the second sheet I have column A with hostnames I need to find the IP and the MASK to fill column B, C, etc.
So the first cell of column B on the second sheet contains =VLOOKUP(A1,Sheet1.A1:I10000,2,0).
When I copy cell B1 formula to cell B2 it correctly changes A1 to A2, but it changes the array value too to “Sheet1.A2:I10001” which is wrongly shifting down the range of the lookup.

M$ Excel support the use of column for the range e.g.: =VLOOKUP(A1,Sheet2!A:I,2,FALSE).

When I try the same using Sheet1.A:I for the array it gives me an error.

I don’t believe this is a bug, but probably just an oversight.

Is there any other way to copy the formula keeping just the search array unchanged?

Thank you for you help,
Cheers,
r

Unfortunately Calc does not allow a full column/row to be used in a formula (like in Excel). This is a limitation in Calc.

However the solution for your particular problem is simple: just “lock” the first cell in the range by adding a $ dollar sign

=VLOOKUP(A1,Sheet1.$A$1:$I$10000,2,0)

(adding a $ to the last cell also locks the end of the block although in your case it might be unnecessary)

This method has been the same since Lotus 123 (back in 1983) for referring to a fixed range.