Help please with lookup from another sheet.

users
#1

Struggling here with VLOOKUP which I hope is the right tool.

I have a column of numbers on one sheet (sheet 1) and on another sheet (sheet 2) a column of numbers with an associated name in adjacent cell. My objective is to find the name associated with the number on sheet 2 and enter it in sheet 1, or put another way: to take (in turn down the column) the number on sheet 1, find this number in the column in sheet 2 and write the name adjacent to that number in cell adjacent to the number I started with in sheet 1.

The numbers are random and not necessarily to be found in second sheet. If no name is found I need some marker entered so I know I must
recourse to a manual search elsewhere.

I think VLOOKUP might be the right tool but got stuck right at the start trying to reference across two sheets. I have both open in same instance of LibreOffice.

Please could somebody confirm my approach and or advise how to proceed.
Budge

#2

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

#3

Hi Brian,

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.

#4

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

Wasn't this covered early in your "Introduction to Spreadsheets" course? Or in the first chapter of your "Spreadsheets for Beginners" book?

To get
=VLOOKUP(A1;Sheet2.$A$1:$B$10;2;0)
in B1:
o Click in B1.
o Type "=VLOOKUP(".
o Click on A1.
o Type ";".
o Click on the sheet tab to reveal the other sheet (whatever it may be called and wherever it is).
o Drag over A1 to B10 on that sheet.
o Type ";2;0)".
o Press Enter or click the green tick in the Input Line.

You may want to click the sheet tab to return to your first sheet at some point in that process, but it is not necessary.

On the [Windows] machine, tab doesn't complete the typing so it is a bit of a pain.

That because Windows users don't type: they gave that up with DOS, some time in the last millennium. You ought to try it.

I trust this helps.

Brian Barker

#5

Hi Brian,
[snip]
It worked but the equation didn't look the same, no $s and ; changed in some cases but it worked. Many thanks.

That because Windows users don't type: they gave that up with DOS, some
time in the last millennium. You ought to try it.

Try what? I have only just scrapped my OS/2 Warp 4 system and Lotus SmartSuite.

Many thanks once more.
Budge.

#6

Yes, sorry - you'd have to interpolate those yourself, of course.

Brian Barker

#7

Are you sure it wasn't just telling you it had a problem saving the
message to the Sent folder?

If that was the error, while you should try to fix it (since you won't
have a copy of the message you sent in the Sent folder), once
Thunderbird gets to the point where it is saving a copy to the Sent
folder, the message was already successfully sent.

#8

Having watched somebody being tutored on how to use Excel, I can
confidently say that it wasn't covered in a course designed to enable a
person to use a spreadsheet in a business environment!

jonathon