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

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.


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?


Carl Paulsen

Dover, NH 03820

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.