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?
Impressum (Legal Info)
: 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