Lookup values
 

I have two separate workbooks. In one workbook there is a code with no
description. In the other, the code includes the description in the next
column. My user wants to insert a formula in Book 1 that will look up the
correct description from Book 2.

Example - Book One

Employee Code Description
101
200
319
101
101
319

Book Two

Employee Code Description
200 Math Teacher
101 Science Teacher
319 Custodian


Does anybody have any idea what kind of formula I use and how to write it? I
want to be able to put a formula in the Description field in Book one that
will find the correct value ANYWHERE in Book 2 (101, 319, etc), then
reference the description and place it in the Description field in Book 1.

Sherry

Check the help menu for VLOOKUP, this will work If all your employee code's
are in the same column


--
Regards,
Dave


"Ladypep" wrote:

I have two separate workbooks. In one workbook there is a code with no
description. In the other, the code includes the description in the next
column. My user wants to insert a formula in Book 1 that will look up the
correct description from Book 2.

Example - Book One

Employee Code Description
101
200
319
101
101
319

Book Two

Employee Code Description
200 Math Teacher
101 Science Teacher
319 Custodian


Does anybody have any idea what kind of formula I use and how to write it? I
want to be able to put a formula in the Description field in Book one that
will find the correct value ANYWHERE in Book 2 (101, 319, etc), then
reference the description and place it in the Description field in Book 1.

Sherry

Hi Sherry

You say separate workbooks, do you truly mean that, or separate sheets?

If separate books then in B2 of Book2
=VLOOKUP(A2,[Book1.xls]Sheet1!$A$2:$B$100,2,0)
change sheet names and ranges to suit.

If just separate sheets in the same boo, then
=VLOOKUP(A2,Sheet1!$A$2:$B$100,2,0)

NOTE, in your example, you have multiple occurrences of code 101 and 319.
The formula will only return the value to the right of the first occurrence.


Regards

Roger Govier


Ladypep wrote:
I have two separate workbooks. In one workbook there is a code with no
description. In the other, the code includes the description in the next
column. My user wants to insert a formula in Book 1 that will look up the
correct description from Book 2.

Example - Book One

Employee Code Description
101
200
319
101
101
319

Book Two

Employee Code Description
200 Math Teacher
101 Science Teacher
319 Custodian


Does anybody have any idea what kind of formula I use and how to write it? I
want to be able to put a formula in the Description field in Book one that
will find the correct value ANYWHERE in Book 2 (101, 319, etc), then
reference the description and place it in the Description field in Book 1.

Sherry

I'm still confused and probably wasn't clear enough on what I'm trying to do.

This is actually in two separate workbooks, not worksheets.

Basically, here is a better run-down:

If the value in cell A1 in Book 1 is found in ANY cell in Book 2, then
return the value of the cell to the right of the cell that was located in
Book 2.

Does that make sense?
--
Sherry


"Roger Govier" wrote:

Hi Sherry

You say separate workbooks, do you truly mean that, or separate sheets?

If separate books then in B2 of Book2
=VLOOKUP(A2,[Book1.xls]Sheet1!$A$2:$B$100,2,0)
change sheet names and ranges to suit.

If just separate sheets in the same boo, then
=VLOOKUP(A2,Sheet1!$A$2:$B$100,2,0)

NOTE, in your example, you have multiple occurrences of code 101 and 319.
The formula will only return the value to the right of the first occurrence.


Regards

Roger Govier


Ladypep wrote:
I have two separate workbooks. In one workbook there is a code with no
description. In the other, the code includes the description in the next
column. My user wants to insert a formula in Book 1 that will look up the
correct description from Book 2.

Example - Book One

Employee Code Description
101
200
319
101
101
319

Book Two

Employee Code Description
200 Math Teacher
101 Science Teacher
319 Custodian


Does anybody have any idea what kind of formula I use and how to write it? I
want to be able to put a formula in the Description field in Book one that
will find the correct value ANYWHERE in Book 2 (101, 319, etc), then
reference the description and place it in the Description field in Book 1.

Sherry

Ladypep wrote...
....
This is actually in two separate workbooks, not worksheets.

Basically, here is a better run-down:

If the value in cell A1 in Book 1 is found in ANY cell in Book 2, then
return the value of the cell to the right of the cell that was located in
Book 2.

If you mean any cell in a particular column in a particular worksheet
in Book 2, then use VLOOKUP, e.g.,

=VLOOKUP(SomeCodeInBook1,'X:\Y\Z\[Book
2.xls]WorksheetNameHere'!$A$2:$B$1000,2,0)

This will locate the value SomeCodeInBook1 in column A in the given
worksheet in Book 2 and return the corresponding value in column B.
Modify file pathname, worksheet name and range as needed.

Hi Sherry

Frm your example, it looked as though you had headings in row 1, that's
why I started iwith A2.
However, I had misread which book was which.
Change the formula round

In cell B1 of Book1 enter

=VLOOKUP(A1,[Book2.xls]Sheet1!$A$2:$B$100,2,0)

Make the range as long as suits you, by altering the 100 as above to any value up to 65536

This will look down column A of Book2 for the value in A1 in Book and, if found will returb the adjacent value from column B of Book2.

I hope this is what you want.

Regards

Roger Govier



Ladypep wrote:

I'm still confused and probably wasn't clear enough on what I'm trying to do.

This is actually in two separate workbooks, not worksheets.

Basically, here is a better run-down:

If the value in cell A1 in Book 1 is found in ANY cell in Book 2, then
return the value of the cell to the right of the cell that was located in
Book 2.

Does that make sense?

Roger,

Although I have just not found the time to get back to my original post to
check for replies, I wanted to tell you that your answer was dead-on and it
works perfectly. Thanks so very much for your help. Now my users think I'm a
genius...lol...but, sigh, I could not take the credit and had to 'fess up
that I had help.

Again thanks.
--
Sherry


"Roger Govier" wrote:

Hi Sherry

Frm your example, it looked as though you had headings in row 1, that's
why I started iwith A2.
However, I had misread which book was which.
Change the formula round

In cell B1 of Book1 enter

=VLOOKUP(A1,[Book2.xls]Sheet1!$A$2:$B$100,2,0)

Make the range as long as suits you, by altering the 100 as above to any value up to 65536

This will look down column A of Book2 for the value in A1 in Book and, if found will returb the adjacent value from column B of Book2.

I hope this is what you want.

Regards

Roger Govier



Ladypep wrote:

I'm still confused and probably wasn't clear enough on what I'm trying to do.

This is actually in two separate workbooks, not worksheets.

Basically, here is a better run-down:

If the value in cell A1 in Book 1 is found in ANY cell in Book 2, then
return the value of the cell to the right of the cell that was located in
Book 2.

Does that make sense?

Hi

You're more than welcome.
Thanks for the feedback and letting me know it all worked OK.

Regards

Roger Govier



Ladypep wrote:

Roger,

Although I have just not found the time to get back to my original post to
check for replies, I wanted to tell you that your answer was dead-on and it
works perfectly. Thanks so very much for your help. Now my users think I'm a
genius...lol...but, sigh, I could not take the credit and had to 'fess up
that I had help.

Again thanks.