Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 07 Aug 2015 12:50:32 +0200How to select entire column while using vlookup function in calchttps://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/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**
Tue, 30 Dec 2014 11:09:37 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/Comment by lolax for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55868#post-id-55868Thanks for your suggestion.Fri, 07 Aug 2015 12:50:32 +0200https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55868#post-id-55868Comment by Lupp for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55800#post-id-55800You 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.Wed, 05 Aug 2015 17:23:52 +0200https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55800#post-id-55800Comment by Kunjomachen for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=53783#post-id-53783@Lupp Yes, I verified it on LO Version: 5.0.0.4
Build ID: cf112dc905650fb985306a7a03d2fe3fcc6c978fTue, 28 Jul 2015 14:49:55 +0200https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=53783#post-id-53783Comment by lolax for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55788#post-id-55788I 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)Wed, 05 Aug 2015 15:29:16 +0200https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=55788#post-id-55788Comment by Lupp for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=53752#post-id-53752@Kunjomachen : This was announced in the release notes in [this place](https://wiki.documentfoundation.org/ReleaseNotes/5.0#A:A_.2F_1:1_entire_column.2Frow_references). I tried to verify it with 5.0.0.0beta3 without success. Did you verify it already with a newer prerelease?Mon, 27 Jul 2015 18:54:04 +0200https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=53752#post-id-53752Answer by ROSt52 for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?answer=44176#post-id-44176I 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
Tue, 30 Dec 2014 12:27:30 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?answer=44176#post-id-44176Comment by Lupp for <p>I made a test in LibO 4.3.5.2 on an XP machine:</p>
<p>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.</p>
<p>As you can see I selected as the area 3 columns A, B, C; the entire array is A2 to C17.</p>
<p>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.</p>
<p>If </p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0) </p>
</blockquote>
<p>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).</p>
<p>If</p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0)</p>
</blockquote>
<p>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</p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44179#post-id-44179@ROSt52 : And again my posting crossed yours.Tue, 30 Dec 2014 12:37:48 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44179#post-id-44179Comment by ROSt52 for <p>I made a test in LibO 4.3.5.2 on an XP machine:</p>
<p>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.</p>
<p>As you can see I selected as the area 3 columns A, B, C; the entire array is A2 to C17.</p>
<p>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.</p>
<p>If </p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0) </p>
</blockquote>
<p>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).</p>
<p>If</p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0)</p>
</blockquote>
<p>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</p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44180#post-id-44180@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?Tue, 30 Dec 2014 12:46:11 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44180#post-id-44180Comment by Lupp for <p>I made a test in LibO 4.3.5.2 on an XP machine:</p>
<p>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.</p>
<p>As you can see I selected as the area 3 columns A, B, C; the entire array is A2 to C17.</p>
<p>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.</p>
<p>If </p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0) </p>
</blockquote>
<p>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).</p>
<p>If</p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0)</p>
</blockquote>
<p>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</p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44181#post-id-44181@ROSt52 : 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.Tue, 30 Dec 2014 12:59:44 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44181#post-id-44181Comment by ROSt52 for <p>I made a test in LibO 4.3.5.2 on an XP machine:</p>
<p>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.</p>
<p>As you can see I selected as the area 3 columns A, B, C; the entire array is A2 to C17.</p>
<p>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.</p>
<p>If </p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0) </p>
</blockquote>
<p>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).</p>
<p>If</p>
<blockquote>
<p>=VLOOKUP(B8,Sheet1!B:D,3,0)</p>
</blockquote>
<p>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</p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44238#post-id-44238@Lupp - Very interesting document! Thanks! Need to take a bit time to understand it. Intellectual challenge I like.Wed, 31 Dec 2014 15:09:34 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?comment=44238#post-id-44238Answer by Lupp for <p>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.</p>
<p><strong>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</strong></p>
https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?answer=44177#post-id-44177**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](/upfiles/14199402453625833.ods)
There is an old feature request filed as bug [FDO#48571](https://www.libreoffice.org/bugzilla/show_bug.cgi?id=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.)Tue, 30 Dec 2014 12:31:15 +0100https://ask.libreoffice.org/en/question/44174/how-to-select-entire-column-while-using-vlookup-function-in-calc/?answer=44177#post-id-44177