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