Ask Your Question

How to select entire column while using vlookup function in calc

asked 2014-12-30 11:09:37 +0100

Kunjomachen gravatar image

updated 2015-07-27 17:51:48 +0100

While using the V Look up function in calc, how do I select the entire column? In google doc the formula =VLOOKUP(B8,Sheet1!B:D,3,0) perfectly works for selecting the entire B,C and D columns. But in Calc the formula returns error.

EDIT: From version 5.0, It is possible to specify references to entire columns or rows using the A:A or 1:1 notation instead of A1:A1048576 or A1:AMJ1

edit retag flag offensive close merge delete


@Kunjomachen : This was announced in the release notes in this place. I tried to verify it with without success. Did you verify it already with a newer prerelease?

Lupp gravatar imageLupp ( 2015-07-27 18:54:04 +0100 )edit

@Lupp Yes, I verified it on LO Version: Build ID: cf112dc905650fb985306a7a03d2fe3fcc6c978f

Kunjomachen gravatar imageKunjomachen ( 2015-07-28 14:49:55 +0100 )edit

I use LO 4.3.7 and I change the settings into Excel A1 for the Formula Syntax from Tools > Options > Libreoffice Calc > Formula and it's okay to use like VLOOKUP(D1,G:I,3,0)

lolax gravatar imagelolax ( 2015-08-05 15:29:16 +0100 )edit

You will get some possibly unwanted changes in the syntax of references this way, too. These may cause formulae using the INDIRECT() function, e.g, to fail. I therefore would disadvise to go this way. In addition: V5.0 will already allow for referencing complete columns/rows (like A:A, 1:4, B:Z) . V5.0.0 will soon be released.

Lupp gravatar imageLupp ( 2015-08-05 17:23:52 +0100 )edit

Thanks for your suggestion.

lolax gravatar imagelolax ( 2015-08-07 12:50:32 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-12-30 12:31:15 +0100

Lupp gravatar image

updated 2015-08-05 17:29:42 +0100

EDITING = Waiting is over! V5.0.0 is released! = References like A:A, 1:5, C:F, 7:7 allowed now! = EDITING

Calc doesn't support the "entire column addressing" (or "entire row") at present. Yo will have to explicitly include the row numbers like in "A1:A1048576". The hard coded number of rows (2^20) will surely not get increased soon. The disadvantages concerning deleted rows can only be avoided, I think, using OFFSET().

See also: ask44177WholeColumnTest001.ods

There is an old feature request filed as bug FDO#48571 on 2012-04-11. There wasn't much support for it, obviously, despite the fact that the topic comes back now and then in the forums.

(The odf documents specify a 'RangeAddress' alternative to that effect for the persistent [file] representation. It may get implemented one day. The feature request, however, is not yet assigned to a developer. You may try to vote it up.)

edit flag offensive delete link more

answered 2014-12-30 12:27:30 +0100

ROSt52 gravatar image

I made a test in LibO on an XP machine:

The formua =VLOOKUP(A2,$Sheet1.$A$2:$C$17,2,0) works correct even when I copy the formula down to the end of the column.

As you can see I selected as the area 3 columns A, B, C; the entire array is A2 to C17.

What could be gone wrong is that Calc does not automatically make the corner points of the 3 column array A2 and C17 as absolute points $A$2 and $C$17. If Google doc does this automatically than you have the reason for not working in LibO.



is your formula in Calc, then make B to $B$1 and D to $D$ end value of your array (bottom most right cell of your array).



is your working formula in Google doc then there is a small difference between Google doc and LibO in the way that Google docs allows you to select an entire column just by using the column names, B, C, D

edit flag offensive delete link more


@ROSt52 : And again my posting crossed yours.

Lupp gravatar imageLupp ( 2014-12-30 12:37:48 +0100 )edit

@Lupp - No problem at all. You added actually information on the request for enhancement, which I was not fully sure about it. I will add an comment at the given bug number. --- Do I understand you correct that ODF states that the RangeAdress should automatically with absolute cell references?

ROSt52 gravatar imageROSt52 ( 2014-12-30 12:46:11 +0100 )edit

@ROSt52 : At he URL you find a metalinguistic production (Backus-Naur form) for references. As I read it, It does allow for whole columns and whole rows either absolute or relative. Please tell me if I was mistaken.

Please note: The syntax is meant for the persistent representation, not for direct use in a Calc sheet.

Lupp gravatar imageLupp ( 2014-12-30 12:59:44 +0100 )edit

@Lupp - Very interesting document! Thanks! Need to take a bit time to understand it. Intellectual challenge I like.

ROSt52 gravatar imageROSt52 ( 2014-12-31 15:09:34 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-12-30 11:09:37 +0100

Seen: 25,404 times

Last updated: Aug 05 '15