# Lookup values

1. ## 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

2. ## RE: Lookup values

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

--
Regards,
Dave

> 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

3. ## Re: Lookup values

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

> 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

4. ## Re: Lookup values

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

>

5. ## Re: Lookup values

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

6. ## Re: Lookup values

Hi Sherry

why I started iwith A2.
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

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

7. ## Re: Lookup values

Roger,

Although I have just not found the time to get back to my original post to
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

Again thanks.
--
Sherry

"Roger Govier" wrote:

> Hi Sherry
>
> why I started iwith A2.
> 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
>
>
>
>
> >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?
> >
> >

>

8. ## Re: Lookup values

Hi

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

Regards

Roger Govier

>Roger,
>
>Although I have just not found the time to get back to my original post to
>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
>
>Again thanks.
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1