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

Sheet 2 would look like:

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


Final sheet:

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