Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 22:06, Jean-Francois Nifenecker a écrit : > Le 15/02/2014 21:14, Pikov Andropov a écrit : >> >> Data > Define Range >> > > There, you define a dataset which can be further managed for data > retrieval, sorting, filtering, mailing... > The column headers are seen as field names and the other rows as records. -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 21:14, Pikov Andropov a écrit : > > Data > Define Range > There, you define a dataset which can be further managed for data retrieval, sorting, filtering, mailing... -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Jean-Francois Nifenecker has written on 2/15/2014 12:46 PM: > Le 15/02/2014 18:20, Pikov Andropov a écrit : >> How do you name an array? >> > > (1) Select the array > > (2) Menu Insert > Names > Define > > (3) Enter the name of your choice (no spaces allowed) and hit OK. > > From now on, using the array in a formula is equivalent to the absolute > reference to this array. > > EG: you select A1:B2 and name it "my_array" > You may write formulae using the $A$1:$B$2 or the my_array syntax (note > that you must not use quotes when dealing with array names in formulae). > That is: > =SUM($A$1:$B$2) > or > =SUM(my_array) > give the same result > > HTH, > Merci, Jean-Francois! What's the difference between this kind of named range and the one you get by doing Data > Define Range in step 2? -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Le 15/02/2014 18:20, Pikov Andropov a écrit : > How do you name an array? > (1) Select the array (2) Menu Insert > Names > Define (3) Enter the name of your choice (no spaces allowed) and hit OK. From now on, using the array in a formula is equivalent to the absolute reference to this array. EG: you select A1:B2 and name it "my_array" You may write formulae using the $A$1:$B$2 or the my_array syntax (note that you must not use quotes when dealing with array names in formulae). That is: =SUM($A$1:$B$2) or =SUM(my_array) give the same result HTH, -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
How do you name an array? On February 15, 2014 11:37:54 AM EST, Carl Paulsen wrote: >Jean-Francois, > >+1 >Thanks for this idea, below. I'd never thought of naming an array, and > >you are right, it would seem to make both generating AND understanding >formulae easier. Plus there's less room for error. In my case I >frequently use the same array for a variety of vlookups, so the added >step of naming could end up saving me some time as well. > >Carl > > >On 2/15/14 2:55 AM, Jean-Francois Nifenecker wrote: >> 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. >> >> > >-- > >Carl Paulsen > >Dover, NH 03820 > > >-- >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 -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
Jean-Francois, +1 Thanks for this idea, below. I'd never thought of naming an array, and you are right, it would seem to make both generating AND understanding formulae easier. Plus there's less room for error. In my case I frequently use the same array for a variety of vlookups, so the added step of naming could end up saving me some time as well. Carl On 2/15/14 2:55 AM, Jean-Francois Nifenecker wrote: 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. -- Carl Paulsen Dover, NH 03820 -- 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
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+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
Re: [libreoffice-users] Calc: help with VLOOKUP
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
Re: [libreoffice-users] Calc: help with VLOOKUP
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? -- Carl Paulsen Dover, NH 03820 -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
If you look at the help for VLOOKUP, the second sentence says "This function checks if a specific value is contained in the first column of an array." Dave On 2/14/2014 12:55, 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
Re: [libreoffice-users] Calc: help with VLOOKUP
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
Re: [libreoffice-users] Calc: help with VLOOKUP
Hmmm. Formatting leaves a little to be desired. Let's see: 1st sheet: Database looks like this: Sheet 1: ID Name Price ID 1Shirt 6 1 2Pants 5 2 3Hat3 3 4Gloves 2 4 Sheet 2: Code Description Min. Bid 3 ;lkajsdfk ;aljsdf 2 3 ;lkjdf 2 4 ;akldjf ;laksf ;alskdfj1 2 oisel 3 1;lakjsdf 4 1 woe 4 4 ldklw 1 Sheet 2 would look like: CodeDescriptionMin. Bid Item Name 3 ;lkajsdfk ;aljsdf 2 Hat 3;lkjdf 2 Hat 4;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3 Pants 1 ;lakjsdf4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves Final sheet: CodeDescriptionMin. BidItem Name 3 ;lkajsdfk ;aljsdf 2 Hat 3;lkjdf 2 Hat 4;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3Pants 1 ;lakjsdf4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves 3 Hat 4 Gloves 4 Gloves 4 Gloves 1 Shirt #N/A #N/A #N/A HTH clarify. Again, I can post this if it helps. Carl On 2/10/14 10:07 PM, Carl Paulsen wrote: OK, the item that you want to look up to return the price or name needs to be in both spreadsheets (otherwise you won't find a match!). IF (big IF here) the ID# and item code are the same for each item, then this could be done as follows: Database looks like this: Sheet 1: ID Name Price ID 1 Shirt 6 1 2 Pants 5 2 3 Hat 3 3 4 Gloves 2 4 Sheet 2: Code Description Min. Bid 3 ;lkajsdfk ;aljsdf 2 3 ;lkjdf 2 4 ;akldjf ;laksf ;alskdfj 1 2 oisel 3 1 ;lakjsdf 4 1 woe 4 4 ldklw 1 (Gibberish was intentional. I didn't want to bother with actual descriptions.) In this case, take the code you're given (3 in the first line, sheet 2) and look up the value in sheet 1 (in this case it would be "Hat"). To do this: Sheet 2, add the following column in D: =VLOOKUP(A2,Sheet1.$A$1:$D$5,2,0) copy and paste this formula as far down the sheet as you need to. Note the dollar symbol is needed to create a static reference to the array that you are looking at for the lookup. Otherwise it starts at the row you're looking at and goes down from there, meaning eventually you'll almost certainly NOT find a match. The lookup value (A2 in the first cell of D2) will change for each row the formula is pasted into. Final sheet 2 would look like this: Code Description Min. Bid Item Name 3 ;lkajsdfk ;aljsdf 2 Hat 3 ;lkjdf 2 Hat 4 ;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3 Pants 1 ;lakjsdf 4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves In Column D, you would see the item name returned, but the actual contents of that column would be the formula above, with the first part after the vlookup (A2) changing with each row from A2 to A3, A4, A5, A6, A7 and A8. What this does is looks up whatever is in A1 and returns the name based on what it matches for ID in sheet 1. Read carefully and I think you'll eventually get it. It can be confusing, but it makes sense if you're careful to follow the logic. Now, after the 7 lines of sample data above, I copied and pasted the vlookup formula into the rest of Column D (as far as you think you need to go). That's the setup. After that, when a number (from 1-4 for the item ID in sheet 1) is typed into column A, an item name is returned in Column D. I typed a bunch of numbers into Column A to illustrate below. See: Code Description Min. Bid Item Name 3 ;lkajsdfk ;aljsdf 2 Hat 3 ;lkjdf 2 Hat 4 ;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3 Pants 1 ;lakjsdf 4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves 3 Hat 4 Gloves 4 -- Carl Paulsen Dover, NH 03820 -- 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://listarchi
Re: [libreoffice-users] Calc: help with VLOOKUP
OK, the item that you want to look up to return the price or name needs to be in both spreadsheets (otherwise you won't find a match!). IF (big IF here) the ID# and item code are the same for each item, then this could be done as follows: Database looks like this: Sheet 1: ID NamePrice ID 1 Shirt 6 1 2 Pants 5 2 3 Hat 3 3 4 Gloves 2 4 Sheet 2: CodeDescription Min. Bid 3 ;lkajsdfk ;aljsdf 2 3 ;lkjdf 2 4 ;akldjf ;laksf ;alskdfj 1 2 oisel 3 1 ;lakjsdf4 1 woe 4 4 ldklw 1 (Gibberish was intentional. I didn't want to bother with actual descriptions.) In this case, take the code you're given (3 in the first line, sheet 2) and look up the value in sheet 1 (in this case it would be "Hat"). To do this: Sheet 2, add the following column in D: =VLOOKUP(A2,Sheet1.$A$1:$D$5,2,0) copy and paste this formula as far down the sheet as you need to. Note the dollar symbol is needed to create a static reference to the array that you are looking at for the lookup. Otherwise it starts at the row you're looking at and goes down from there, meaning eventually you'll almost certainly NOT find a match. The lookup value (A2 in the first cell of D2) will change for each row the formula is pasted into. Final sheet 2 would look like this: CodeDescription Min. BidItem Name 3 ;lkajsdfk ;aljsdf 2 Hat 3 ;lkjdf 2 Hat 4 ;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3 Pants 1 ;lakjsdf4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves In Column D, you would see the item name returned, but the actual contents of that column would be the formula above, with the first part after the vlookup (A2) changing with each row from A2 to A3, A4, A5, A6, A7 and A8. What this does is looks up whatever is in A1 and returns the name based on what it matches for ID in sheet 1. Read carefully and I think you'll eventually get it. It can be confusing, but it makes sense if you're careful to follow the logic. Now, after the 7 lines of sample data above, I copied and pasted the vlookup formula into the rest of Column D (as far as you think you need to go). That's the setup. After that, when a number (from 1-4 for the item ID in sheet 1) is typed into column A, an item name is returned in Column D. I typed a bunch of numbers into Column A to illustrate below. See: CodeDescription Min. BidItem Name 3 ;lkajsdfk ;aljsdf 2 Hat 3 ;lkjdf 2 Hat 4 ;akldjf ;laksf ;alskdfj 1 Gloves 2 oisel 3 Pants 1 ;lakjsdf4 Shirt 1 woe 4 Shirt 4 ldklw 1 Gloves 3 Hat 4 Gloves 4 Gloves 4 Gloves 1 Shirt 1 Shirt 2 Pants 2 Pants 2 Pants #N/A #N/A #N/A #N/A #N/A Note that the #N/A values in Column D are columns for which no "code" has been entered yet. The formula can't return a value since there's nothing in the lookup column A. Also note that we didn't try to lookup the Description for Column B (that data doesn't exist in Sheet 1 so it wouldn't work anyway). We also didn't try to look up the price (column C of sheet 1) but could have done that with a new lookup formula in a column E in sheet 2. I hope this makes sense. I can send you the sample I created (or post to Nabble if I can get the time to do this...). Let me know if you want me to send you the sample file directly (I won't do so until I hear from you). If others want to see it too, let me know and I'll just post to Nabble (I don't think I have an account yet, but will figure it out). Carl On 2/10/14 4:00 PM, Dave Liesse wrote: 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. Using your specific example, the VLOOKUP function would be used in column E of Sheet 2. If you were wanting to also copy the price from Sheet 1 (which you've said you're not), you would need another VLOOKUP in Column F. This can be set up in advance, but you'll need to use an exact lookup unless you know you exact lookup range ahead of time (in which case you don't need to worry about searching an empty area). I know this didn't answer all your questions, but others will chime in. Dave On 2/10/2014 12:03, Pikov Andropov wrote: Something is not working for me. Hope someone can help. I have a two-sheeted speadsheet. Sheet 1 has three columns as follows:
Re: [libreoffice-users] Calc: help with VLOOKUP
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. Interesting. I don't think I've ever seen that stated. > Using your specific example, the VLOOKUP function would be used in > column E of Sheet 2. So I type the VLOOKUP into a cell in E. Where do I enter the ID code? > This can be set up in advance, but you'll need to use an exact lookup > unless you know you exact lookup range ahead of time (in which case you > don't need to worry about searching an empty area). ??? Is there a problem if I overstate the size of the lookup area??? Thanks. > On 2/10/2014 12:03, Pikov Andropov wrote: >> Something is not working for me. Hope someone can help. >> >> I have a two-sheeted speadsheet. >> >> Sheet 1 has three columns as follows: >> A. Name >> B. Price (ignorable as far as this example is concerned) >> C. ID number >> >> Sheet 2 has three columns as follows: >> A. Item Code >> B. Item description >> C. Minimum bid (ignorable as far as this example is concerned) >> >> The operator will be given a piece of paper with an Item code, an ID >> number and a price. I would like him to be able to look up an Item code >> in Sheet 2 and then, in that row, enter the ID number (column D?). A >> VLOOKUP formula would paste the Name into an available column (E?) and >> the operator would enter the price into the next column (F?). >> >> Where do I type the =VLOOKUP formula? >> >> Can I save the sheet with the formula so I can set this strategy up >> ahead of time? >> >> How do I get the formula to apply properly to every row? >> >> What should the initial formula look like? >> >> Thank you. >> > > -- 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
Re: [libreoffice-users] Calc: help with VLOOKUP
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. Using your specific example, the VLOOKUP function would be used in column E of Sheet 2. If you were wanting to also copy the price from Sheet 1 (which you've said you're not), you would need another VLOOKUP in Column F. This can be set up in advance, but you'll need to use an exact lookup unless you know you exact lookup range ahead of time (in which case you don't need to worry about searching an empty area). I know this didn't answer all your questions, but others will chime in. Dave On 2/10/2014 12:03, Pikov Andropov wrote: Something is not working for me. Hope someone can help. I have a two-sheeted speadsheet. Sheet 1 has three columns as follows: A. Name B. Price (ignorable as far as this example is concerned) C. ID number Sheet 2 has three columns as follows: A. Item Code B. Item description C. Minimum bid (ignorable as far as this example is concerned) The operator will be given a piece of paper with an Item code, an ID number and a price. I would like him to be able to look up an Item code in Sheet 2 and then, in that row, enter the ID number (column D?). A VLOOKUP formula would paste the Name into an available column (E?) and the operator would enter the price into the next column (F?). Where do I type the =VLOOKUP formula? Can I save the sheet with the formula so I can set this strategy up ahead of time? How do I get the formula to apply properly to every row? What should the initial formula look like? Thank you. -- 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
