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
@Kunjomachen : This was announced in the release notes in this place. I tried to verify it with 5.0.0.0beta3 without success. Did you verify it already with a newer prerelease?
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)
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.
Thanks for your suggestion.
I made a test in LibO 4.3.5.2 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.
If
=VLOOKUP(B8,Sheet1!B:D,3,0)
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).
If
=VLOOKUP(B8,Sheet1!B:D,3,0)
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
@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?
@ROSt53 : At he URL
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.pdf#page=44&zoom=auto,113.1,456.7 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 - Very interesting document! Thanks! Need to take a bit time to understand it. Intellectual challenge I like.
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.)