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+unsubscr...@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
