Ask Your Question
0

VLOOKUP by columns [closed]

asked 2014-10-21 11:52:58 +0100

B1r0 gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 09:48:01.503868

1 Answer

Sort by » oldest newest most voted
0

answered 2014-10-21 12:14:15 +0100

Pedro gravatar image

updated 2014-10-21 12:16:13 +0100

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-10-21 11:52:58 +0100

Seen: 12,518 times

Last updated: Oct 21 '14