Selecting Entire Columns for VLOOKUP

5 replies [Last post]
Otter
Offline
Last seen: 1 year 46 weeks ago
Title:
Joined: 27 Sep 2012
Posts: 8

Is there no way to select A:B, as an example in a formula in LibreOffice? I seem to be expected to select a specific range of cells for my formula (A1:B45), but this isn’t economical when I’m handling a hundred thousand of rows of data.

SHIFT+CTRL+DOWN is one way to get around it, but if there are spaces, seperated chunks of data or any other irregularities in the list of data this can be tedious.

Otter
Offline
Last seen: 1 year 46 weeks ago
Title:
Joined: 27 Sep 2012
Posts: 8
Really? No easy method that I

Really? No easy method that I may be overlooking? LibreOffice doesn’t handle full cell column/row selection?

Lupp
Offline
Last seen: 8 hours 57 min ago
Title: ★★★★★
Joined: 1 Aug 2012
Posts: 616
Guesses

Could you be a bit more precise?
What is really meant by “… but if there are spaces, seperated chunks of data or any other irregularities in the list of data …” ?
If a column contains something inhibiting the use of VLOOKUP on all the column, what difference how it was selected?

One hint: You may select a column (or two) first and copy the range shown in the editable control leftmost in the “Formula Bar” and paste it to the proper parameter position of your function. For the columns A and B this is “A1:B1048576”.

Be modest with huge ranges. Calc must have a chance to cope with the task.


Read you later! Lupp from Munich

Otter
Offline
Last seen: 1 year 46 weeks ago
Title:
Joined: 27 Sep 2012
Posts: 8
To put it more simply, my

To put it more simply, my concern is that a very standard formula such as this:

=VLOOKUP

Isn’t recognized by LibreOffice because the exact range of cells in the array isn’t specified. One must select the exact range (A1:B15) for it to function, and while this seems like a minor additional task when working with small arrays, it’s not economical when scaled to hundreds, thousands or tens of thousands of rows of data. So I was hoping this wasn’t an oversight and I was just missing some simple function when working with formulas in Libre.

Your hint does solve the issue I imagine. Selecting first the columns I need to parse, then grabbing that absolute cell range in the cell range field, but does that hold the same dynamic functionality as A:B does in Excel? Will my formula actually try parsing through A1:B1048576 absolutely even though I only have values in A1:B:54000? Because A:B in Excel functions in that it parses only the range that contains actual values.

Otter
Offline
Last seen: 1 year 46 weeks ago
Title:
Joined: 27 Sep 2012
Posts: 8
Not sure why it put my

Not sure why it put my VLOOKUP formula in a hover-over text there. :/

(C1,A:B,1,Innocent

Lupp
Offline
Last seen: 8 hours 57 min ago
Title: ★★★★★
Joined: 1 Aug 2012
Posts: 616
My Poor Understanding

Seems there is something I do not understand.
Maybe that is partly because I did not use Excel for a long time now.
But: In what way (format of parameter-value) you tell the program to LOOKUP a whole column should not affect the efficiency of the search. Where there is implemented the better optimization for such search (Excel / Star-, Open-, Libre- Office) is another question.
If you can be satisfied by the speed Calc completes the task should be made a matter of some testing. I do not very often use big lookups – but other cases where my spreadsheets contain formulas which cause a lot of parsing and calculating let me hope for you.

Some combinations of characters containing parentheses and punctuation marks are abused in this editor for creating smileys and other “special effects”. I too would prefer to have every character coding for itself.


Read you later! Lupp from Munich

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.