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

Le 14/02/2014 23:06, Carl Paulsen a écrit :
> 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.

Another way of achieving that is to name the array. This has my
preference because a named array makes formulae easier to understand.

> 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.

+1 to all of these. The 0 (FALSE) parameter is the one I always use by
default for the reason you stated (getting an error when no match is found).

-- 
Jean-Francois Nifenecker, Bordeaux

-- 
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