Re: [libreoffice-users] Help please with lookup from another sheet.

Hi Brian,

On 01/02/17 16:55, Brian Barker wrote:
> At 16:13 01/02/2017 +0000, Budge Noname wrote:
> > Struggling here with VLOOKUP which I think is the right tool. I have a
> > column of numbers on one sheet (sheet 1) ...
>
> Let's say they are in column A.
>
> > ... and on another sheet (sheet 2) a column of numbers with an
> > associated name in adjacent cell.
>
> Let's say they are in columns A (numbers) and B (names).
>
> > My objective is to find the name associated with the number on sheet 2
> > and enter it in sheet 1, ...
>
> Let's say in column B.
>
> > I think VLOOKUP might be the right tool ...
>
> Yup.
>
> > ... but got stuck right at the start trying to reference across two
> > sheets. I have both open in same instance of LibreOffice.
>
> If you have two sheets they are part of they same document. Perhaps you
> mean two spreadsheet documents? You can do either.
>
> > The numbers are random and not necessarily to be found in second sheet.
>
> In B1, enter:
> =VLOOKUP(A1;Sheet2.$A$1:$B$10;2;0)
> and fill it down the column.
> o A1 because we are looking for the entry corresponding to A1.
> o A1:B10 (say) is the table of numbers and names.
> o 2 is the identifier of the column of the table (the second) containing
> the required names.
> o 0 (or FALSE) indicates that the numbers in column A of your first
> sheet may not be sorted.
>
> > If no name is found I need some marker entered so I know I must
> > recourse to a manual search elsewhere.
>
> You will get #N/A free of charge: that may do. If you want something
> else, try:
> =IFNA(VLOOKUP(A1;Sheet2.$A$1:$B$10;2;0);"Missing name!")
>
> If the name table is in a separate document, you may find it simpler to
> copy that into your main document or to create a reference there to the
> entire table first.
>
> Oh, and the answers to your other three questions are remarkably similar.

Many thanks and apologies to the list.  The multiple posting was because 
I was using a Windoze machine and Thunderbird was telling me the message 
had not been sent each time I tried.  (Half an hour on phone to ISP 
still didn't sort it so I am doing email on Linux machine)

Many thanks for the answer.  One supplementary question, the when 
created each sheet is in a different file.  I have imported on into the 
other to get them in the same file but the sheet tab names are the 
filenames not "sheet 1" or "sheet 2."  Do I have to type in the full 
filename?  In parenthesis?   On the windoze machine, tab doesn't 
complete the typing so it is a bit of a pain.

Will try it meanwhile and thanks again,
Budge.



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