Lookups across worksheets

[sixpence668]

I need to consolidate some information, using several worksheets in one file. Here's an idea of what I need to do:

Worksheet 1 -

First | Last | CompanyID | LocationID
Bill | Smith | 1 | 3
Jen | Jones | 2 | 1

Worksheet 2 -

Company ID | Name
1 | Acme
2 | Widgets
3 | Bucky's

Worksheet 3 -

LocationID | Name
1 | New York
2 | Paris
3 | Rome

I need to come up with a set of formulas that give me the following result:

Worksheet 1 -

First | Last | CompanyID | LocationID
Bill | Smith | Acme | Rome
Jen | Jones | Widgets | New York

Any ideas would be greatly appreciated!!

Jessica

[Max]

One set-up to try ..

In a new Sheet4
------------
Copy > paste Sheet1's headers into A1:D1
> _FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_

Put in A2: =IF(Sheet1!A2="","",Sheet1!A2)
Copy across to B2

Put in C2:
=IF(Sheet1!C2="","",VLOOKUP(Sheet1!C2,Sheet2!A:B,2,0))

Put in D2:
=IF(Sheet1!D2="","",VLOOKUP(Sheet1!D2,Sheet3!A:B,2,0))

Select A2:D2, fill down to say, D100,
to cover the max expected data range that'll be in Sheet1

Sheet4 will return the desired results:

> _First__|_Last__|_CompanyID_|_LocationID_
> Bill | Smith | Acme | Rome
> Jen | Jones | Widgets | New York
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"sixpence668" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to consolidate some information, using several worksheets in one
> file. Here's an idea of what I need to do:
>
> WORKSHEET 1 - [/B]
>
> _FIRST_|__LAST__|_COMPANYID_|_LOCATIONID_
> BILL | SMITH | 1 | 3
> JEN | JONES | 2 | 1
>
> [B]WORKSHEET 2 -
>
> _Company_ID_|__Name_
> 1 | Acme
> 2 | Widgets
> 3 | Bucky's
>
> WORKSHEET 3 -
>
> _LocationID___|__Name_
> 1 | New York
> 2 | Paris
> 3 | Rome
>
> I need to come up with a set of formulas that give me the following
> result:
>
> WORKSHEET 1 -
>
> _First__|_Last__|_CompanyID_|_LocationID_
> Bill | Smith | Acme | Rome
> Jen | Jones | Widgets | New York
>
> Any ideas would be greatly appreciated!!
>
> Jessica
>
>
> --
> sixpence668
> ------------------------------------------------------------------------
> sixpence668's Profile:
http://www.excelforum.com/member.php...o&userid=25472
> View this thread: http://www.excelforum.com/showthread...hreadid=389209
>

[sixpence668]

That worked perfectly! Thank you so much!

[Max]

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"sixpence668" <[email protected]>
wrote in message
news:[email protected]...
>
> That worked perfectly! Thank you so much!
>
>
> --
> sixpence668
> ------------------------------------------------------------------------
> sixpence668's Profile:
http://www.excelforum.com/member.php...o&userid=25472
> View this thread: http://www.excelforum.com/showthread...hreadid=389209
>