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

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.

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