Lookups across worksheets

  • Thread starter Thread starter sixpence668
  • Start date Start date
S

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 - [/B]

_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
 
One set-up to try ..

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

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
Click to expand...
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"sixpence668" <[email protected]>
wrote in message
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

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
Click to expand...
 
Back
Top