Re: [libreoffice-users] LOOKUP with 2 values in 2 columns

Agh, I should have thought of this myself. Simple and elegant...

Thanks Brian,

Rob.

On 8 jun. 2015, at 14:58, Brian Barker wrote:

> At 13:56 08/06/2015 +0200, Rob Jasper wrote:
> > I have a list of music numbers for our choir in calc. Each number is identified by a number in 
> > col A, and sometimes a sub-number or letter in col B. Something like:
> >
> > 296             title1  Composer        Arr.    text    publisher
> > 297     a       title2  C2
> > 297     b       title3  C3
> > 298     1       title4  C1
> > 298     2       title5  C3              A       B       xx
> > 299             title6  C2
> >
> > This is in the lab Mainlist. In another tab (performed) I just want to reference number and 
> > subnumber, and get the title, composer, arranger, etc. from the Main list. How can I lookup 2 
> > values set in Performed E1 and F1 in the main list?
>
> Insert an extra column - I'll call it "C" - between the subnumbers and the titles. In Cn, enter
> =An&Bn
> This will concatenate the values in the first two columns, to give "297a", "2982", etc. You can 
> hide that column if you prefer, so it does not need to display or print.
>
> To harvest the title, use:
> =VLOOKUP(E1&F1;Mainlist.C1:H999;2;0)
> To harvest the composer:
> =VLOOKUP(E1&F1;Mainlist.C1:H999;3;0)
> and so on .
>
> I trust this helps.
>
> Brian Barker
>
>
> -- 
> 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


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