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      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       
        
        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:
> >     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.

--

Carl Paulsen

8 Hamilton Street

Dover, NH 03820

(603) 749-2310


--
To unsubscribe e-mail to: users+unsubscribe@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