Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


Thank you, both!

Carl Paulsen has written on 2/14/2014 5:06 PM:
Take a look at the Help entry for vlookup.  That entry begins with the 
first 3 lines:

Vertical search with reference to adjacent cells to the right. This 
function checks if a specific value _is contained in the first column 
of an array_. The function then returns the value in the same row of 
the column named by Index.


Underlining added.  In other words, it looks for values in the first 
column of the named array, then returns the value in the same row in 
whatever column you tell it to (the "Index" column).  I'm sure it could 
be stated more clearly and more specifically in the details of the Help 
entry, but it's there.

A few hints for vlookups:  I find I usually want to add "hard" 
references for the array so that it always looks at the intended rows of 
the array.  To do that, you need to add $ before each cell reference in 
the array.  So if the array of the function is A1:C100, I enter it as 
$A$1:$C$100.  This forces vlookup to always look at those cells 
regardless of what row the calculation is happening on.

Without this fixed reference, as the vlookup moves to new cells (e.g. 
down a column) the array will also shift down.  So for example, if the 
looked up value is in cell D1 and the vlookup formula is in E1, the 
above array will be searched.  But if you then copy that function down 
column E, each cell you move down will shift the Array down as a 
relative reference.  That is, on line E2, the vlookup will look at D2, 
then search for that value in the array A2:C101 (not the originally 
intended A1:C100).  On row 201, the vlookup in E201 will look at the 
value in D201 and search for it in the array A201:C300.  If the values 
you want to search through are only in A1:C100, then your lookup won't 
find a match...ever.  The $ "hard" references prevent this.

Finally, be mindful of the implications of the sort order variable in 
the function.  If it is 1 or TRUE, the array must be sorted ascending, 
and the function returns the nearest value "below" or "before" the 
looked up value.  If you want to force it to return only exact matches 
and/or don't want to or can't sort the array, be sure to set sort order 
to 0 or FALSE.

HTH,
Carl



On 2/14/14 3:55 PM, Pikov Andropov wrote:
Dave Liesse has written on 2/10/2014 4:00 PM:
The first problem I see is that your lookup value -- in this case the ID
number -- has to be the first column in the lookup array.
When I moved the ID number to the first column, my VLOOKUP worked! Where
does it say that that is a requirement?





-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.