Re: [libreoffice-users] Calc: help with VLOOKUP

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